I removed the next snippet from Profiler (from the statement that fails because of "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 understand it fails due to the subquery, because after i make use of a hardcoded value, it really works. Therefore, maybe there's an issue of asynchronous processing or perhaps a general syntax error. T-SQL 2000.

Please advise a remedy. Thanks!

Yeah, I do not think use a subquery like a parameter to some saved procedure. The spec for that Professional command states that value ought to be a object name, character string.

Either use another variable to keep the outcomes, or alter the saved procedure right 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 maybe systecsys_get_next_ticket is really 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)

Used another var to keep the need for the subquery in also it labored.