Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

IN Clause Question Expand / Collapse
Author
Message
Posted Monday, August 6, 2012 11:51 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 4, 2014 10:06 PM
Points: 235, Visits: 320
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
*/




Post #1341016
Posted Monday, August 6, 2012 11:57 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, August 14, 2014 6:01 AM
Points: 885, Visits: 259
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
Post #1341018
Posted Tuesday, August 7, 2012 12:08 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 4, 2014 10:06 PM
Points: 235, Visits: 320
Thanks, your solution makes sense.


Post #1341020
Posted Tuesday, August 7, 2012 12:22 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1341028
Posted Tuesday, August 7, 2012 9:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:50 AM
Points: 13,082, Visits: 12,547
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1341339
Posted Tuesday, August 7, 2012 9:56 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 7:42 AM
Points: 3,688, Visits: 72,435
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.




--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #1341374
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse