August 6, 2012 at 11:51 pm
The top part of this works. I'm trying to pass to an "IN" list on the bottom. What is the cleanest way to do this? I'd like the set to be like this if possible 'dog', 'mouse', 'cat'
IF OBJECT_ID('tempdb..#Rookie') > 0 DROP TABLE #Rookie
CREATE TABLE #Rookie
(Id varchar(10), CriteriaA int, CriteriaB varchar(20))
INSERT INTO #Rookie
(Id, CriteriaA, CriteriaB)
SELECT 3111, 1, 'dog' UNION ALL
SELECT 4111, 1, 'mouse' UNION ALL
SELECT 5111, 1, 'cat' UNION ALL
SELECT 6111, 2, 'fly' UNION ALL
SELECT 7111, 3, 'bug'
SELECT * FROM #Rookie
WHERE CriteriaA IN (1, 2)
AND CriteriaB IN ('dog', 'mouse', 'cat', 'fly')
--this does not work
/*
DECLARE @CriteriaA int, @CriteriaB varchar(MAX)
SET @CriteriaA = 1, 2
SET @CriteriaB = ('dog', 'mouse', 'cat')
SELECT * FROM #Rookie
WHERE CriteriaA IN @CriteriaA
AND CriteriaB IN @CriteriaB
*/
August 6, 2012 at 11:57 pm
You have wrongly declared @criteria as int. It should be varchar. First you have to form a query. Then execute it..
Pls check the below code.
DECLARE @CriteriaA VARCHAR(10), @CriteriaB varchar(MAX)
SET @CriteriaA = '1, 2'
SET @CriteriaB = '(''dog'', ''mouse'', ''cat'')'
declare @sql varchar(max)
Set @sql = 'SELECT * FROM #Rookie
WHERE CriteriaA IN (' + @CriteriaA + ') AND CriteriaB IN '+ @CriteriaB
Print @sql
exec(@sql)
Thanks
August 7, 2012 at 12:08 am
Thanks, your solution makes sense.
August 7, 2012 at 12:22 am
Personally I wouldn't use Dynamic SQL for this.
My suggestion is to pass the two lists of values (@CriteriaA and @CriteriaB) as delimited strings and use a string splitter like Jeff Moden's (community) DelimitedSplit8K function to split them and pass that result into the IN.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 7, 2012 at 9:02 am
dwain.c (8/7/2012)
Personally I wouldn't use Dynamic SQL for this.My suggestion is to pass the two lists of values (@CriteriaA and @CriteriaB) as delimited strings and use a string splitter like Jeff Moden's (community) DelimitedSplit8K function to split them and pass that result into the IN.
+1000000
There are very few absolutes in sql server but directly executing a parameter is one of them. If you use the dynamic sql approach you took your procedure and made it instantly vulnerable to sql injection.
Since you posted in the 2008 forum I would suggest that and even better approach than parsing this is to use a table valued parameter instead.
_______________________________________________________________
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/
August 7, 2012 at 9:56 am
Must agree with Sean and Dwain.
Either pass in separated values and split the strings to a temporary table/table variable and join to that, or if you feel comfortable pass in a tvp and use it directly in the join.
IN righfully sucks with parameters.... IN also with large quantities of values tends to suck on performance.
IN is nothing more than shorthand for multiple OR = 's tests.... enough of those will choke a query.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply