SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Using variables within SP_executeSQL?? Error msg
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

meadow0
Starting Member

USA
6 Posts

Posted - 03/20/2013 :  14:37:45  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4612 Posts

Posted - 03/20/2013 :  17:00:23  Show Profile  Reply with Quote
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+'',%''' 
END

PRINT @SQL

Edited by - Lamprey on 03/20/2013 17:00:53
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000