T-SQL：如何在动态SQL中使用参数？

发布于 2024-07-25 12:21:04 字数 706 浏览 7 评论 0原文

我有以下动态查询，无需 WHERE 子句即可正常工作，该子句需要 UNIQUEIDENTIFIER。

当我传递它时，我没有得到结果。我尝试了 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

原文

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

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

明媚如初 2024-08-01 12:21:05

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

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

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

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

回复收藏 0 原文

秋千易 2024-08-01 12:21:05

我不确定您的变量是否以字符串格式或二进制格式填充，但您可能需要在 where 子句中引用 uniqueidentifier。如果您只选择 uniqueidentifier 字段，它会以字符串还是二进制形式返回？

回复收藏 0 原文

套路撩心 2024-08-01 12:21:04

您必须将参数传递给 sp_executesql。有关详细信息，请参阅 MSDN< /a>.

...
 WHERE 
    CreatedBy = @p
...

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

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

回复收藏 0 原文

撩人痒 2024-08-01 12:21:04

多参数语法。也许这会为某人节省额外的 Google 搜索次数：

exec sp_executesql 
    @qry, 
    N'@value1 int, @value2 int, @currentValue int', 
    @value1 = @value1, @value2 = @value2, @currentValue = @currentValue

Multiple parameter syntax. Maybe this will save someone an extra Google Search:

exec sp_executesql 
    @qry, 
    N'@value1 int, @value2 int, @currentValue int', 
    @value1 = @value1, @value2 = @value2, @currentValue = @currentValue

回复收藏 0 原文

~没有更多了~