CODEWITHSUNDEEP
sql-serverdynamic-sql
Mist
Mist

Reputation: 684

SQLServer: Dynamic sql raise exception Could not find stored procedure

This is my dynamic sql.

DECLARE @SQL varchar(MAX)
DECLARE @Data varchar(MAX)
SET @Data='ALFKI'' OR ContactName=''Ana Trujillo'''
SET @SQL='select * from Customers Where CustomerID='''+@Data+''

print @SQL
exec (@SQL)

when i print then i get this sql select * from Customers Where CustomerID='ALFKI' this sql is right one but when i replace print @SQL with exec @SQL and execute the dynamic sql again then i am getting error called

Msg 2812, Level 16, State 62, Line 8 Could not find stored procedure 'select * from Customers Where CustomerID='ALFKI''.

not clear where i made the mistake. please give me some hint where is the problem in above dynamic sql. thanks

Upvotes: 2

Views: 1267

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

There is EXEC to execute a stored procedure. Of course there is no SP with the name select * from Customers....

And there is EXEC(), a function!, which is used to execute dynamically created statements.

Just use EXEC(@SQL) instead.

Another way with some more options is sp_executesql with wide support for parameters. You can use this to pass the ALFKI as parameter. Otherwise you might be open for SQL injection...

Upvotes: 3

Related Questions