CODEWITHSUNDEEP
sqlsql-serversql-server-2008
selva1990
selva1990

Reputation: 3

Syntax error in sql exec count

declare @message int,@DBName varchar(50)    
set @DBName ='AutoChip'    

exec('select '+@message+'= count(*) from '+@DBname+'.[dbo].[Report List]')    

print @message

Getting an error trying to print the count

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '='.

I will pass DBname dynamically, I am using a cursor

Upvotes: 0

Views: 158

Answers (1)

Sateesh Pagolu
Sateesh Pagolu

Reputation: 9606

The variable name needs to be part of dynamic SQL you are creating, but it's shouldn't be concated with it. For example, if your variable is of type varchar and has value as 'ERROR', resultant query will be 

select ERROR= count(*) from YOURDBNAME.[dbo].[Report List]

so the correct one is below. 

 exec('select @message= count(*) from '+@DBname+'.[dbo].[Report List]')

You need to include variable declaration and initialization in dynamic sql. You cannot set a value of external variable inside a dynamic sql as the context of execution will be different.

Upvotes: 2

Related Questions