sql server sp_executesql sql 字符串错误
我有一个看起来像这样的字符串：
set @sqlstring = N'select @mindate = min(time), @maxdate = max(time) from ' + @st_churn_active_table;
我打印它，它看起来像这样：
select @mindate = min(time), @maxdate = max(time) from derp.derp_table
我使用如下参数定义运行 sp_executesql：
execute sp_executesql @sqlstring, N'@maxdate date,@mindate date'
它的错误如下：
The parameterized query '(@maxdate date,@mindate date)select @mindate = min(time), @maxda' expects the parameter '@maxdate', which was not supplied.
String 变成
'(@maxdate日期,@mindate日期)选择@mindate = min(时间),@maxda'
sql字符串被切断，有谁知道为什么以及如何解决这个问题？
谢谢！
I have a string that looks like this:
set @sqlstring = N'select @mindate = min(time), @maxdate = max(time) from ' + @st_churn_active_table;
I print it and it looks like this:
select @mindate = min(time), @maxdate = max(time) from derp.derp_table
I run sp_executesql with parameter definitions like this:
execute sp_executesql @sqlstring, N'@maxdate date,@mindate date'
It errors like this:
The parameterized query '(@maxdate date,@mindate date)select @mindate = min(time), @maxda' expects the parameter '@maxdate', which was not supplied.
String becomes
'(@maxdate date,@mindate date)select @mindate = min(time), @maxda'
The sql string is cut off, does anyone know why and how to fix this?
Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
请参阅：在 SQL Server 中使用 sp_executesql 存储过程时如何指定输出参数
See: How to specify output parameters when you use the sp_executesql stored procedure in SQL Server
首先，sql server 看到整个字符串。只有错误消息会截断它。请注意，即使截断的 sql 字符串仅包含名称的一部分，该错误也正确获取了缺失参数的名称。
其次，这不会如你所愿。 您不能以 sp_executesql 的方式使用输出参数。
但最后还是要解决为什么会抛出异常的问题一个错误。我不能确定，但我怀疑这里的问题是存在类型不匹配，因此它无法使用您提供的参数。我希望 sql server 在这种情况下会抛出更好的错误消息（抱怨类型不匹配），但我没有方便测试的 sql server，而且我想不出任何其他原因会导致这种情况问题。
First of all, sql server saw the whole string. It's only the error message that truncates it. Notice the error got the name of the missing parameter right, even though the truncated sql string only had part of the name.
Second, this isn't going to work how you want it to. You can't use output parameters that way with sp_executesql.
But finally to the problem of why it's throwing an error. I can't be sure, but I suspect the problem here is that there is a type mismatch, and so it can't use the parameter you gave it. I would hope that sql server would throw a better error message (complain about a type mismatch) in that situation, but I don't have sql server handy to test with and I can't think of any other reason for this to cause a problem.