我将向存储过程传递一个表值输入参数，以及一个包含查询字符串的变量，所以我的存储过程是这样的。
CREATE PROCEDURE [dbo].[SP_SelectData_View]
(
@Sort VARCHAR(MAX),
@CONDITION VARCHAR(MAX) = ''
@Values dbo.FlowStatus READONLY
)
AS
BEGIN
DECLARE @STRQUERY NVARCHAR(MAX)
IF @CONDITION IS NOT NULL AND @CONDITION != ''
BEGIN
SET @CONDITION = 'WHERE ' + @CONDITION
END
ELSE
BEGIN
SET @CONDITION = ''
END
IF @Sort IS NULL OR @Sort = ''
BEGIN
SET @Sort = 'Id Desc'
END
BEGIN
SET @STRQUERY = 'SELECT A.*
FROM ' + @Values + ' as FlowStatus'
JOIN Tbl_A as A
ON A.status = FlowStatus.StatusNowId AND B.flow = FlowStatus.FlowNowId
' + @CONDITION + '
Order By ' + @Sort
EXEC(@STRQUERY)
END
END
但在上面的代码中，我得到了一个错误
必须声明标量变量@Values
我已经搜索过了，我认为这是因为别名没有被检测到，因为它在字符串中。但如果我没有把它放在字符串查询中，@condition和@sort变量就会出错。有没有一种解决方案可以同时调用表值变量和查询字符串变量？
正如我和其他人所评论的那样，您目前的方法有几个问题，Brent Ozar在动态SQL方面有很好的参考https://www.brentozar.com/sql/dynamic/
我想说，不要传入一些SQL，在存储过程中构造它；传入where中使用的name等参数，因此我给出了一个完整的工作示例。这还展示了如何将用户定义的表类型传递到存储过程中，然后将其传递到动态SQL中。
我希望这是一个足够好的技术例子，我有一点时间，所以我想我会尽可能多地尝试和帮助：(
/*
--------------------------------------------
Create a test table to run the stored proc against
*/
IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'MyTestTable'))
BEGIN
PRINT 'Creating table MyTestTable'
CREATE TABLE [dbo].[MyTestTable](
Id BIGINT NOT NULL IDENTITY(1,1) PRIMARY KEY,
[Name] NVARCHAR(50) NOT NULL
)
INSERT INTO dbo.MyTestTable ([Name])
VALUES ('Andrew'),
('Bob'),
('john')
-- SELECT * FROM MyTestTable
END
GO
/*
--------------------------------------------
Create the table type that we pass into the store proc
*/
IF NOT EXISTS (SELECT * FROM sys.types WHERE is_table_type = 1 AND name = 'FlowStatus')
BEGIN
PRINT 'Creating type [dbo].[FlowStatus]'
CREATE TYPE [dbo].FlowStatus AS TABLE (
MyId BIGINT PRIMARY KEY,
SomeText NVARCHAR(200)
)
END
GO
/*
--------------------------------------------
Create the stored proc with the User Defined table type
*/
CREATE OR ALTER PROCEDURE [dbo].[MyStoredProc]
(
@SortBy VARCHAR(50),
@SearchName VARCHAR(50),
@Values dbo.FlowStatus READONLY
)
AS
BEGIN
-- As your SQL gets more complex it is an idea to create seperate parts of the SQL
DECLARE @SqlToExecute NVARCHAR(MAX)
-- The primary data you want to get
SET @SqlToExecute = N'
SELECT T.Id, T.[Name], V.SomeText
FROM MyTestTable AS T
LEFT JOIN @Values AS V ON V.MyId = T.Id
WHERE 1 = 1' -- We do this so that we can have many AND statements which could be expanded upon
IF @SearchName IS NOT NULL
BEGIN
SET @SqlToExecute = @SqlToExecute + N'
AND T.[Name] LIKE ''%' + @SearchName + ''''
END
IF @SortBy IS NOT NULL
BEGIN
SET @SqlToExecute = @SqlToExecute + N'
ORDER BY ' +
CASE WHEN @SortBy LIKE 'Name%' THEN N'T.[Name]'
ELSE N'T.[Id]'
END
END
-- Print out the script that will be run, useful for debugging you code
PRINT @SqlToExecute
EXEC sp_executesql @SqlToExecute,
N'@Values dbo.FlowStatus READONLY', @Values
END
GO
/*
--------------------------------------------
Now lets test it
-- Test Andrew
*/
DECLARE @flowStatusType AS dbo.FlowStatus
INSERT INTO @flowStatusType(MyId, SomeText)
VALUES(1, 'Test1'),
(2, 'Test2')
EXEC [dbo].[MyStoredProc] @SearchName = 'Andrew', @SortBy = 'Name', @Values = @flowStatusType
GO
-- Test Bob
DECLARE @flowStatusType AS dbo.FlowStatus
INSERT INTO @flowStatusType(MyId, SomeText)
VALUES(1, 'Test1'),
(2, 'Test2')
EXEC [dbo].[MyStoredProc] @SearchName = 'Bob', @SortBy = 'Name', @Values = @flowStatusType
GO
同样值得注意的是，如果您可以在不需要动态SQL的情况下加入@Values，那么这肯定会减少工作量。