使用查询的结果集作为存储过程调用中的参数

发布于 2024-12-06 01:22:47 字数 464 浏览 2 评论 0原文

我从 Profiler 中提取了以下代码片段（来自由于“附近的语法错误”而失败的语句）：

exec sp_executesql @statement = N'CREATE TABLE --other stuff...

  DECLARE @student_id_ticket INT
  EXEC @student_id_ticket = systecsys_get_next_ticket (select top 1 table_id from systecsys_table where name like ''%Student_List%''), ''n'', 1

--INSERT statement using this value and other stuff

我知道它因子查询而失败，因为当我使用硬编码值时，它会起作用。因此，可能存在异步处理问题或一般语法错误。 T-SQL 2000。

请提出补救措施。谢谢！

原文

I extracted the following snippet from Profiler (from a statement that fails due to "syntax error near ,"):

exec sp_executesql @statement = N'CREATE TABLE --other stuff...

  DECLARE @student_id_ticket INT
  EXEC @student_id_ticket = systecsys_get_next_ticket (select top 1 table_id from systecsys_table where name like ''%Student_List%''), ''n'', 1

--INSERT statement using this value and other stuff

I know that it fails because of the subquery, because when I use a hardcoded value, it works. Therefore, it could be that there is a problem of asynchronous processing or a general syntax error. T-SQL 2000.

Please suggest a remedy. Thanks!

分享到QQ

分享到微博

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

发布评论

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

那小子欠揍 2024-12-13 01:22:47

是的，我认为您不能使用子查询作为存储过程的参数。 EXEC 命令的规范规定value 应该是对象名称、字符串。

要么使用另一个变量来存储结果，要么将存储过程更改为函数。

DECLARE @student_id_ticket INT, @table_id INT
SELECT @table_id = (select top 1 table_id from systecsys_table where name like ''%Student_List%'')
EXEC @student_id_ticket = systecsys_get_next_ticket @table_id, ''n'', 1

或者如果 systecsys_get_next_ticket 是一个函数：

DECLARE @student_id_ticket INT
SELECT @student_id_ticket = systecsys_get_next_ticket((select top 1 table_id from systecsys_table where name like ''%Student_List%''), ''n'', 1)

Yeah, I don't think you can use a subquery as a parameter to a stored procedure. The spec for the EXEC command states that value should be a object name, character string.

Either use another variable to store the results, or change the stored procedure into a function.

DECLARE @student_id_ticket INT, @table_id INT
SELECT @table_id = (select top 1 table_id from systecsys_table where name like ''%Student_List%'')
EXEC @student_id_ticket = systecsys_get_next_ticket @table_id, ''n'', 1

or if systecsys_get_next_ticket is a function:

DECLARE @student_id_ticket INT
SELECT @student_id_ticket = systecsys_get_next_ticket((select top 1 table_id from systecsys_table where name like ''%Student_List%''), ''n'', 1)

回复收藏 0 原文