November 29, 2011 at 9:05 am
I have the following table structure for keeping Attribute Flags for products
CREATE TABLE [dbo].[Features](
[FeatureId] [int] IDENTITY(1,1) NOT NULL,
[Sku] [nvarchar](50) NOT NULL,
[Flag1] [bit] NOT NULL,
[Flag2] [bit] NOT NULL,
[Flag3] [bit] NOT NULL,
[Flag4] [bit] NOT NULL,
[Flag5] [bit] NOT NULL,
[Flag6] [bit] NOT NULL,
[Flag7] [bit] NOT NULL,
[Flag8] [bit] NOT NULL
CONSTRAINT [PK_FeatureId] PRIMARY KEY CLUSTERED
(
[FeatureId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
What I am trying to do is query only attributes that are "True" dynamically. A user will select 1 or more product attributes and the result set should return all products that have those features. The result set should be inclusive. In other words, the products must have all of the selected features regardless of whether or not they have additional features that the user did not select.
So, if a user selects Flag1, Flag4 and Flag 5 the resulting query should be
SELECT * FROM Features WHERE Flag1 =1 and Flag4 =1 and Flag5 = 1
The problem is in my stored procedure, I need to pass all Flag Attributes as parameters based on the users selections. How can I build this query without having to resort to dynamic SQL?
CREATE PROCEDURE [dbo].[pSelectProductByFeatures]
(
@Flag1bit
,@Flag2bit
,@Flag3bit
,@Flag4bit
,@Flag5bit
,@Flag6bit
,@Flag7bit
,@Flag8bit
)
AS
SELECT *
FROM Features
WHERE Flag1 = @Flag1 and
Flag2 = @Flag2 and
Flag3 = @Flag3 and
Flag4 = @Flag4 and
Flag5 = @Flag5 and
Flag6 = @Flag6 and
Flag7 = @Flag7 and
Flag8 = @Flag8
Sample Proc Call
exec pSelectProductByFeatures @Flag1=1, @Flag2=0, @Flag3=0, @Flag4=1, @Flag5=1, @Flag6=0, @Flag7=0
I was thinking about writing a bunch of IF statements and build the query dynamically, but I know there has to be a more elegant way of handling this. Any help is much appreciated.
November 29, 2011 at 9:11 am
INSERT INTO [Features] VALUES ('sku1',1,1,1,0,0,0,0,0)
INSERT INTO [Features] VALUES ('sku2',1,1,1,1,1,1,0,0)
INSERT INTO [Features] VALUES ('sku3',1,1,1,0,0,0,0,1)
INSERT INTO [Features] VALUES ('sku4',0,0,1,0,0,0,0,0)
INSERT INTO [Features] VALUES ('sku5',0,0,1,0,0,0,0,0)
INSERT INTO [Features] VALUES ('sku6',0,1,1,0,0,1,1,0)
INSERT INTO [Features] VALUES ('sku7',1,1,1,1,0,0,0,0)
INSERT INTO [Features] VALUES ('sku8',1,0,0,1,1,0,0,0)
INSERT INTO [Features] VALUES ('sku9',1,1,0,0,0,0,0,0)
November 29, 2011 at 9:15 am
Gail has a great blog post on this type of query. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]
_______________________________________________________________
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/
November 29, 2011 at 9:17 am
So if the flag is 0, then do not check that column... correct?
Then ...
SELECT *
FROM Features
WHERE
(Flag1 = @Flag1 OR @Flag1 = 0) and
(Flag2 = @Flag2 OR @Flag2 = 0) and
(Flag3 = @Flag3 OR @Flag3 = 0) and
(Flag4 = @Flag4 OR @Flag4 = 0) and
(Flag5 = @Flag5 OR @Flag5 = 0) and
(Flag6 = @Flag6 OR @Flag6 = 0) and
(Flag7 = @Flag7 OR @Flag7 = 0) and
(Flag8 = @Flag8 OR @Flag8 = 0)
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 29, 2011 at 9:37 am
Errr, that seemed really simple! Here I am thinking functions and dynamical SQL...
Let me test that code out
November 29, 2011 at 9:38 am
November 29, 2011 at 9:40 am
You are welcome. The query Jason put together is probably the same thing I would have come up with but I figured that the article was worth a read either way. Her blog is a good source of information.
_______________________________________________________________
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/
November 29, 2011 at 9:41 am
Daniel H (11/29/2011)
Errr, that seemed really simple! Here I am thinking functions and dynamical SQL...Let me test that code out
It's that old saying "You can't see the forest for the horse drinking the water" :hehe:
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 29, 2011 at 9:42 am
Sean Lange (11/29/2011)
You are welcome. The query Jason put together is probably the same thing I would have come up with but I figured that the article was worth a read either way. Her blog is a good source of information.
Worth the read indeed. 🙂
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgViewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply