CODEWITHSUNDEEP
sqldatabaset-sqlsql-server-2000
ppecher
ppecher

Reputation: 1988

Using a the result-set of a query as a parameter in a stored proc invocation

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!

Upvotes: 1

Views: 64

Answers (2)

samjudson
samjudson

Reputation: 56893

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)

Upvotes: 1

ppecher
ppecher

Reputation: 1988

Used another var to store the value of the subquery in and it worked.

Upvotes: 0

Related Questions