CODEWITHSUNDEEP
sqlsql-servert-sql
Nick Stavrou
Nick Stavrou

Reputation: 46

What is the proper way to execute Dynamic SQL?

We have lots of SQL scripts in our codebase, which produce dynamic SQL statements, and we execute these statements against the database with

EXEC (@FINALSQL)

It is declared like this

 DECLARE @FINALSQL NVARCHAR(MAX);

In one of our scripts, we now get an error when executing the dynamic SQL:

Error Number: 2812
Error Severity: 16
ErrorState: 62
ErrorMessage: Could not find stored procedure

with the SQL statement following.

The error number is related to the the error message but still couldn't find any related issue of how to resolve this problem.

I have also read this question but it didn't help because I already enclosed dynamic SQL in brackets calling EXEC() generates error: could not find stored procedure

Any ideas what could have caused the problem?

Update: the use of @FINALSQL is to create Updates for an amount on tables based on condition in the script.

Upvotes: 0

Views: 2774

Answers (1)

Randy in Marin
Randy in Marin

Reputation: 1143

The answer to almost anything dynamic can be found in Erland Sommarskog's tour de force.

http://www.sommarskog.se/dynamic_sql.html

"Execute" is optional for the first statement in a batch when executing a stored procedure. There is likely some formatting error in the string and the parser is trying to interpret the first word as a procedure name after failing to parse it otherwise.

If you have no option to capture the string, then use an Extended Event session or trace to capture the query for errors.

If it was a real procedure, then I 'd suspect that current database is wrong. A "USE mydatabase;" as the first thing in the string would fix that. But you state this is not the case. Also, the procedure name was not listed in the error message.

Upvotes: 2

Related Questions