T-SQL：如何在动态SQL中使用参数？ [英] T-SQL: How to use parameters in dynamic SQL?

问题描述

我有以下动态查询，它在没有 WHERE 子句的情况下工作正常，该子句期望 UNIQUEIDENTIFIER 。

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

当我将其传递时，没有得到结果。我尝试了 CAST 和 CONVERT ，但没有结果。我可能做错了，有人可以帮忙吗？

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

推荐答案

必须将参数传递给sp_executesql。 有关详细信息，请参见MSDN 。

You must pass in the parameters to sp_executesql. See MSDN for details.

...
 WHERE 
    CreatedBy = @p
...

EXECUTE sp_executesql @sql, N'@p UNIQUEIDENTIFIER', @p = @p_CreatedBY

这篇关于T-SQL：如何在动态SQL中使用参数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！