August 18, 2011 at 1:44 pm
I have a table, ColorPlate, with following fields:
1. id int.
2. Plate varchar(100)
3. ColorType int.
There are three color types, Red(1), Blue(2), Yellow(4), depends on the color combinations, it can have 7 different values, from 1 to 7. For example, if a user selected Red and Yellow, then ColorType value is 5. then I need to select the plates with both Red and Yellow color. I can get the records if only one type of color is requested. But having troubles to find the the way to select both red and yellow.
Here is my SQL statement:
/*
Declare @ColorType int
Select * From ColorPlate
Where ColorType =
CASE when @ColorType = 1 then 1
when @ColorType = 2 then 2
when @ColorType = 4 then 4
when @ColorType = 3 then ?
when @ColorType = 5 then ?
when @ColorType = 6 then ?
when @ColorType = 7 then ?
END)
Order by Plate;
*/
August 18, 2011 at 1:56 pm
This seems like an awfully complicated way to handle getting more than record. Why don't you just pass the plates that the user wants? The way you built this you are going to run dynamic sql out the wazoo to pull this off. You can't have a case statement return more than 1 value (as you are noticing). You can pass multiple values in as a single parameter. You will just have to split them out inside your proc. If this is the road you want (which is what i would suggest) you can read an awesome article here[/url] about how to set this up.
_______________________________________________________________
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 18, 2011 at 2:19 pm
This should do it for you.
Declare @ColorType INT = 3
Select cp1.*
From ColorPlate cp1
CROSS APPLY ColorPlate cp2
CROSS APPLY ColorPlate cp3
Where cp1.colortype & cp2.colortype & cp3.colortype & @ColorType <> 0
Order by ColorID;
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 18, 2011 at 2:31 pm
Cool idea. Will have to add to my snippets.
_______________________________________________________________
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 18, 2011 at 2:33 pm
Sean Lange (8/18/2011)
Cool idea. Will have to add to my snippets.
Thanks - it actually made me think of an article to write
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 18, 2011 at 2:35 pm
Looking forward to it.
_______________________________________________________________
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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply