Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
meadow0
Starting Member
6 Posts |
Posted - 2013-03-20 : 14:37:45
|
Hi, My query is much more complex than I'll post here, but I'm unable to use a variable while using an sp_executeSQL ... is there a workaround?declare @system_status varchar(30)select @system_status = '12,20'declare @sql nvarchar(4000) select @sql = 'SELECT [system_status] FROM VW_Document_Main WHERE 1=1 ' IF @system_status IS NOT NULL AND @system_status NOT IN ('-1','0') BEGIN SELECT @sql = @sql + 'and (',''+@system_Status+'',') LIKE ''%,''+system_Status+'',%''' I'm attempting to concatenate a string onto the end of the SQL and eventually query it by using sp_executesql However, I get an error message with regards to my concatenation"A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations."Any ideas on how to fix it / work around?? |
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-03-20 : 17:00:23
|
[code]declare @system_status varchar(30)select @system_status = '12,20'declare @sql nvarchar(4000)select @sql = 'SELECT [system_status] FROM VW_Document_Main WHERE 1=1 ' IF @system_status IS NOT NULL AND @system_status NOT IN ('-1','0') BEGIN SELECT @sql = @sql + 'and ('','+@system_Status+','') LIKE ''%,''+system_Status+'',%''' ENDPRINT @SQL[/code] |
|
|
|
|
|
|
|