March 20, 2013 at 7:44 am
Hi Guys,
I'm looking to use an SP_ExecuteSQL that would allow me to retrieve the following, and add some additional spice to the query when needed. Doing it this way allows for me to add complexities to it in the future. I have about 9 parameters in my query, but only list one since all the rest will follow suit.
I'm looking to use a comma delimited string that allows me to select multiple variables in a parameter. However, I'm not sure how to add this into my query below:
declare @system_status varchar(30)
select @system_status = '12'
declare @sql nvarchar(4000)
select @sql = 'SELECT [system_status]
FROM VW_Document_Main
where 1=1 '
if @System_Status = '-1'
Begin
select @sql = @sql + 'and system_status <> 20'
End
else if @system_status is not null and @system_status not in ('-1','0')
Begin
select @sql = @sql + 'and ','+REPLACE(@system_Status,'','')+',' LIKE '%,'+system_Status+',%''
I run into several issues:
Firstly, the addition to the @sql must be in a string concatenation or else it won't recognize the column name "system_Status" however, when it is in the string, I seem to be having issues with the commas.
When I run the query without the @SQL variable it works fine:
declare @system_status varchar(30)
select @system_status = '9,20'
SELECT [system_status]
FROM VW_Document_Main
WHERE 1=1
AND ','+REPLACE(@system_Status,'','')+',' LIKE '%,'+system_Status+',%'
I'm sure it just has something to do with some misplace commas, or bad script. Any guesses as to how to attack?
March 20, 2013 at 8:12 am
I see a few issues going on here. First you are taking in parameters to build a dynamic string and you going to end up executing that dynamic sql with no parameters. This is vulnerable to sql injection.
Your big line of dynamic sql has a lot of errors in it.
select @sql = @sql + 'and ','+REPLACE(@system_Status,'','')+',' LIKE '%,'+system_Status+',%''
I think that line should be something like this:
select @sql = @sql + 'and REPLACE(' + @system_Status + ','''','''') LIKE ''%'' + system_Status + ''%'''
You have a replace statement that is used that is doing nothing. Your replace statement is: REPLACE(@system_Status,'','')
Here you are finding nothing and replacing it with nothing. Not sure what you are trying to do there but this isn't going to do anything.
The query you have built is nonSARGable because you have like % VALUE %. That means it will have to scan each and every row to see if meets the criteria.
I think that what you are trying to do here is process a query with a comma delimited list of values? The best approach to this is to first parse that string and then do your evaluations.
You should read the article in my signature about splitting strings. You might also want to take a look at the first link in my signature for best practices when posting questions.
_______________________________________________________________
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/
March 20, 2013 at 8:23 am
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....
Just to get a better understanding of what your string is doing:
select @sql = @sql + 'and REPLACE(' + @system_Status + ','''','''') LIKE ''%'' + system_Status + ''%'''
The concatenation begins with 'and correct?
Why is it that you're using plus signs around @system_Status?
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?
Sorry for all the questions, I'd like to be able to work through these myself without help in the future, and I'm attempting to learn
March 20, 2013 at 8:34 am
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/
March 20, 2013 at 8:46 am
I'm actually noticing now, that your originally revised query string does not allow for multiple parameters in @system_Status. I can enter as many arguments as I want, and it'll execute, but still will only take the first of them. Hmm...
March 20, 2013 at 9:06 am
I believe it should conceptually look something like:
select @sql = @sql + 'and ' + @system_Status + ' LIKE ''%,'' + system_Status + '',%'''
but I'm generating an error: An expression of non-boolean type specified in a context where a condition is expected, near ','.
March 20, 2013 at 9:21 am
meadow0 (3/20/2013)
I'm actually noticing now, that your originally revised query string does not allow for multiple parameters in @system_Status. I can enter as many arguments as I want, and it'll execute, but still will only take the first of them. Hmm...
No. The whole approach your taking here for multiple parameters is not a good one. It will work but you really need to read the article about splitting strings. The performance of what you are doing here is going to be horrible. If you have even a few thousand rows this will be noticeable.
_______________________________________________________________
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/
March 20, 2013 at 9:28 am
I find the article conceptually and technically difficult to understand as I've only been working with SQL for about two weeks. I will certainly attempt to give it a read if it'll be that beneficial. Thanks
March 20, 2013 at 9:38 am
meadow0 (3/20/2013)
I find the article conceptually and technically difficult to understand as I've only been working with SQL for about two weeks. I will certainly attempt to give it a read if it'll be that beneficial. Thanks
Understood. It is pretty technical and the topic is certainly advanced. Kudos to you for not only reading it but also not blindly using it because some guy on the internet said "it was best".
The performance problem you will run into is called SARGable. This comes from "search argumentable".
Think of your queries as though it is you looking through the phone book.
With what you are doing you want to find any of a list of possible values and those values are contained in a larger string. To use the phone book example you would have to look at every single name in the phone book to determine if the name is in your string. If instead you first parse your string you are no looking for absolute values. This means that sql can use its built in indexing. So if you don't have a value in your list that begins with the letter 'a', it won't even look in that part because it would be a waste of time. It can also find values like 'smith' far easier. It doesn't have to start with the first page and keep going until it finds smith. It will instead skip ahead large chunks of pages because searching the pages prior to that are pointless. Not sure if I am explaining this well, but hopefully you get the idea.
If you want some help with coding this you will need to post some ddl (create table scripts), sample data (insert statements) and desired output based on your sample data. The first article in my signature gives several example of the best way to post this stuff.
_______________________________________________________________
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/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply