meadow0 (3/20/2013)
Sean, that worked perfect...I understood the Replace function was doing nothing, I had written it at a time when I was in fact replacing something....
Then you should remove it. 🙂
Why is it that you're using plus signs around @system_Status?
Because you need this to be built into your dynamic string. Remember that what you are doing is building a string that will be executed. The scope of your variable will be outside your execution of the dynamic sql.
Furthermore, why in the REPLACE function did you change the last two inputs to '''' as opposed to the original '' ... is this actually replacing something now?
Now it isn't actually replacing anything now. It is however going to produce a valid string. Remember that in order to get a single quote inside a string variable you have to escape it.
You should probably add some selects somewhere along the way so you can see the dynamic string you have built.
declare @system_status varchar(10) = 'status'
select 'and REPLACE(' + @system_Status + ','''','''') LIKE ''%'' + system_Status + ''%'''
Now the bigger issue really is that this is WAY slower than parsing the string using the approach found by following the article in my signature about splitting strings.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/