T-SQL：如何在动态 SQL 中使用参数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1036745/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
T-SQL: How to use parameters in dynamic SQL?
提问by Yaser Ahmed
I have the following dynamic query which is working fine without the WHEREclause, which is expecting UNIQUEIDENTIFIER.
我有以下动态查询，它在没有WHERE子句的情况下工作正常，这是期待UNIQUEIDENTIFIER.
When I pass it in, I don't get a result. I tried CASTand CONVERT, but no result. I might be doing it wrong, can anybody help?
当我传递它时，我没有得到结果。我试过CAST和CONVERT，但没有结果。我可能做错了，有人可以帮忙吗？
CREATE PROCEDURE [dbo].[sp_Test1] /* 'b0da56dc-fc73-4c0e-85f7-541e3e8f249d' */
(
@p_CreatedBy UNIQUEIDENTIFIER
)
AS
DECLARE @sql NVARCHAR(4000)
SET @sql ='
DECLARE @p_CreatedBY UNIQUEIDENTIFIER
SELECT
DateTime,
Subject,
CreatedBy
FROM
(
SELECT
DateTime, Subject, CreatedBy,
ROW_NUMBER() OVER(ORDER BY DateTime ) AS Indexing
FROM
ComposeMail
WHERE
CreatedBy = @p_CreatedBy /* <--- the problem is in this condition */
) AS NewDataTable
'
EXEC sp_executesql @sql
回答by Tomalak
You must pass in the parameters to sp_executesql. See MSDN for details.
您必须将参数传递给 sp_executesql。有关详细信息，请参阅 MSDN。
...
WHERE
CreatedBy = @p
...
EXECUTE sp_executesql @sql, N'@p UNIQUEIDENTIFIER', @p = @p_CreatedBY
回答by OJisBad
Multiple parameter syntax. Maybe this will save someone an extra Google Search:
多参数语法。也许这会为某人节省额外的 Google 搜索：
exec sp_executesql
@qry,
N'@value1 int, @value2 int, @currentValue int',
@value1 = @value1, @value2 = @value2, @currentValue = @currentValue
回答by Dimi Takis
DECLARE @ParmDefinition NVARCHAR(500)
SET @ParmDefinition = '@p_CreatedBy UNIQUEIDENTIFIER'
EXEC sp_executesql @sql, @ParmDefinition, @p_CreatedBy = @p_CreatedBy
回答by Lazy Bob
I'm not sure if your variable is getting populated in string format or binary, but you may need to quote the uniqueidentifier in your where clause. If you just select the uniqueidentifier field, does it come back as string or binary?
我不确定您的变量是以字符串格式还是二进制填充，但您可能需要在 where 子句中引用 uniqueidentifier。如果您只选择 uniqueidentifier 字段，它会以字符串还是二进制形式返回？
