如何解决sp_executesql中的参数问题

发布于 2024-09-26 02:50:06 字数 392 浏览 3 评论 0原文

我有以下查询：

create proc [dbo].GetCustById
as
DECLARE @sql nvarchar(500)
DECLARE @Param  nvarchar(200)
SET @sql = 'select @columnName from customer where custId = @custId'
SET @Param = N'@columnName varchar(10), @custId int'

EXEC sp_executesql @sql, @Param , @columnName = 'Address1', @custId = '42'

但它总是返回字符串“Address1”而不是 Address1 列的值。有人可以帮忙吗？

谢谢

原文

I have the following query:

create proc [dbo].GetCustById
as
DECLARE @sql nvarchar(500)
DECLARE @Param  nvarchar(200)
SET @sql = 'select @columnName from customer where custId = @custId'
SET @Param = N'@columnName varchar(10), @custId int'

EXEC sp_executesql @sql, @Param , @columnName = 'Address1', @custId = '42'

But it always return a string "Address1" instead of the value of Address1 column. Anyone can help?

thanks

分享到QQ

分享到微博

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

发布评论

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

桜花祭 2024-10-03 02:50:06

参数会根据数据类型立即转义 - 这就是为什么您得到返回值“Address1”而不是列的实际值。

不要将列名作为参数提交，而是作为连接字符串提交：

DECLARE @sql nvarchar(500)
DECLARE @Param  nvarchar(200)
SET @sql = 'select '+ @columnName +' from customer where custId = @custId'
SET @Param = N'@custId int'

EXEC sp_executesql @sql, @Param , @custId = 42

在此处了解有关行为的更多信息。

我知道的唯一其他替代方案要求您使用决策逻辑重定向到静态定义列名的查询：

IF @columname = 'Address1' 
BEGIN

  SET @sql = 'select Address1 from customer where custId = @custId'

END

A parameter is immediately escaped based on the data type--that's why you're getting the value "Address1" returned rather than the actual value for the column.

Submit the column name not as a parameter, but as a concatenated string:

DECLARE @sql nvarchar(500)
DECLARE @Param  nvarchar(200)
SET @sql = 'select '+ @columnName +' from customer where custId = @custId'
SET @Param = N'@custId int'

EXEC sp_executesql @sql, @Param , @custId = 42