-1
dECLARE @LS_SQL CHAR(100)
dECLARE @SQL varCHAR(max)
SET @LS_SQL=ltrim('''STOCK IN HAND'',''STORE'',''PRODUCT''')

set @SQL='SELECT * FROM ITEM WHERE GROUPNAME IN(' + rtrim(@LS_SQL) + ')'
PRINT @SQL
execute @SQL

result

SELECT * FROM ITEM WHERE GROUPNAME IN('STOCK IN HAND','STORE','PRODUCT') Msg 2812, Level 16, State 62, Line 9 Could not find stored procedure 'SELECT * FROM ITEM WHERE GROUPNAME IN('STOCK IN HAND','STORE','PRODUCT')'.

Robert
  • 25,425
  • 8
  • 67
  • 81
Vinod John
  • 69
  • 1
  • 2
  • 11
  • did you ever read the error message yourself? It clearly says that STORED PROCEDURE IS NOT FOUND. you are running direct query on EXECUTE Method which expect a store procedure name. – Sumit Gupta Aug 16 '13 at 08:35

3 Answers3

2

This command

execute @SQL

run a procedure. If you wan to run dynamic sql you should use below command:

exec (@SQL)

you can also use 

execute sp_sqlexec @SQL
Robert
  • 25,425
  • 8
  • 67
  • 81
1

I suggest to use sp_executesql, like:

exec sp_executesql @stmt = @SQL

you can see more help here Dynamic SQL - EXEC(@SQL) versus EXEC SP_EXECUTESQL(@SQL)

Community
  • 1
  • 1
Roman Pekar
  • 107,110
  • 28
  • 195
  • 197
0

try this

dECLARE @LS_SQL CHAR(100)
dECLARE @SQL varCHAR(max)
SET @LS_SQL=ltrim('''STOCK IN HAND'',''STORE'',''PRODUCT''')

set @SQL='SELECT * FROM ITEM WHERE GROUPNAME IN(' + rtrim(@LS_SQL) + ')'
PRINT @SQL
execute sp_sqlexec @SQL
Low Chee Mun
  • 610
  • 1
  • 4
  • 9