运行 sp_executesql 查询需要参数@statement [英] Running sp_executesql query expects parameter @statement
问题描述
我不知道如何解决这个错误:
I'm not sure how to resolve this error:
过程或函数sp_executesql"需要未提供的参数@statement".
对于这个查询:
DECLARE @a INT
DECLARE @b VARCHAR
SET @a = 1
WHILE @a < 30
BEGIN
set @b = @a
exec sp_executesql update source_temp set pmt_90_day = pmt_90_day + convert(money,'trans_total_'+@b)+N'
N'where convert(datetime,'effective_date_'+@b)+N' <= dateadd(day,90,ORSA_CHARGE_OFF_DATE)
and DRC_FLAG_'+@b = 'C'''
SET @a = @a + 1
END
另外,你能帮我理解 N' 的正确用法吗,以及它在这个语句中是否正确使用.
Also, can you help me understand the proper usage of N' and if its done correctly in this statement.
感谢您的帮助.
推荐答案
sp_executesql"存储过程需要单个字符串参数@statement 来执行.
The "sp_executesql" stored proc expects a single string parameter @statement to execute.
您的字符串在这里完全不正常.....您需要在任何固定"字符串部分前面加上 N'......' 以使其成为 Unicode 字符串,但这绝对不是正确的案例在这里.
Your string is totally out of whack here..... you need to prefix any "fixed" string parts with a N'......' to make them Unicode strings, but this is definitely not properly the case here.
我想你可能想试试这个:
I think you might want to try this:
DECLARE @a INT
DECLARE @b VARCHAR(2)
SET @a = 1
DECLARE @statement NVARCHAR(500)
WHILE @a < 30
BEGIN
SET @b = CAST(@a AS VARCHAR(2))
SET @statement =
N'update source_temp set pmt_90_day = pmt_90_day + ' +
'convert(money, ''trans_total_' + @b + ''') ' +
'where convert(datetime, ''effective_date_' + @b + ''')' +
' <= DATEADD(DAY, 90, ORSA_CHARGE_OFF_DATE) ' +
'and DRC_FLAG_' + @b + ' = ''C'''
exec sp_executesql @statement
SET @a = @a + 1
END
这是否有效并达到您的预期?
Does this work and do what you expect it to do??
说明:N'.........' 分隔包含 SQL 语句的整个字符串.必须复制(转义")内的任何撇号.我希望我能正确理解你的逻辑.
Explanation: the N'.........' delimits the whole string which contains the SQL statement. Any apostrophe inside that has to be duplicated ("escaped"). I hope I understood your logic correctly.
马克
这篇关于运行 sp_executesql 查询需要参数@statement的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!