-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
  • 24,847
  • 8
  • 64
  • 77
Vinod John
  • 55
  • 1
  • 2
  • 10
  • 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
  • 24,847
  • 8
  • 64
  • 77
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
  • 99,839
  • 26
  • 181
  • 193
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
  • 600
  • 1
  • 3
  • 9