T-SQL:如何在动态SQL中使用参数?

I have the following dynamic query which is working fine without the WHERE clause, which is expecting UNIQUEIDENTIFIER.

我有下面的动态查询，没有WHERE子句就可以正常工作，它期望的是惟一标识符。

When I pass it in, I don't get a result. I tried CAST and CONVERT, but no result. I might be doing it wrong, can anybody help?

当我传递它时，我不会得到结果。我试过转换，但没有结果。我可能做错了，有人能帮忙吗?

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

3 个解决方案

#1

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

#2

DECLARE @ParmDefinition NVARCHAR(500)
SET @ParmDefinition = '@p_CreatedBy UNIQUEIDENTIFIER'

EXEC sp_executesql @sql, @ParmDefinition, @p_CreatedBy = @p_CreatedBy

#3

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子句中引用惟一标识符。如果您只选择了uniqueifier字段，那么它会以字符串形式还是二进制形式回归呢?