[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

QUERY (...) WHERE ID=1 AND ID=2 AND ID=3 AND (...) FROM DYNAMIC TO STATIC QUERY

Posted on 2007-10-01
5
Medium Priority
?
258 Views
Last Modified: 2010-03-19
suppose i have this query:

SELECT * FROM CARS
INNER JOIN FEATURES ON FEATURE_CAR_ID = CAR_ID
WHERE
FEATURE_ID = 1 AND
FEATURE_ID = 2 AND
FEATURE_ID = 4 AND
FEATURE_ID = 6 AND
FEATURE_ID = 7

Where the "where" clause will may contain no feature_id or can contain many.. will depend on the by program rules..

so.. is my only option to do this by dynamic query ? or is there anyway to make a procedure from this ?

i was thinking on something like this

declare @test nvarchar(2000)
set @test = '1, 2, 3, 4'
SELECT * FROM CARS
INNER JOIN FEATURES ON FEATURE_CAR_ID = CAR_ID
WHERE FEATURE_ID In @test

but of course that didnt work.. (and also the 'In' works like this right? feature_id = 1 OR feature_id =2 ... )
so does any1 know how could i do this? besides doing dynamic query  

thx
0
Comment
Question by:eguilherme
  • 3
  • 2
5 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19994049
create the following function:
CREATE FUNCTION dbo.ParmsToList (@Parameters varchar(500))
returns @result TABLE (Value varchar(30))
AS  
begin
     DECLARE @TempList table
          (
          Value varchar(30)
          )

     DECLARE @Value varchar(30), @Pos int

     SET @Parameters = LTRIM(RTRIM(@Parameters))+ ','
     SET @Pos = CHARINDEX(',', @Parameters, 1)

     IF REPLACE(@Parameters, ',', '') <> ''
     BEGIN
          WHILE @Pos > 0
          BEGIN
               SET @Value = LTRIM(RTRIM(LEFT(@Parameters, @Pos - 1)))
               IF @Value <> ''
               BEGIN
                    INSERT INTO @TempList (Value) VALUES (@Value) --Use Appropriate conversion
               END
               SET @Parameters = RIGHT(@Parameters, LEN(@Parameters) - @Pos)
               SET @Pos = CHARINDEX(',', @Parameters, 1)

          END
     END    
     INSERT @result
     SELECT value
        FROM @TempList
     RETURN
END    


and your query goes like this:

declare @test nvarchar(2000)
set @test = '1, 2, 3, 4'
SELECT * FROM CARS
INNER JOIN FEATURES ON FEATURE_CAR_ID = CAR_ID
WHERE FEATURE_ID In ( select value from dbo.parmstolist( @test) )


0
 
LVL 10

Author Comment

by:eguilherme
ID: 19994179
hm.. but like i said b4.. the In operator works with "OR"

so if it find just 1 of the values, then it returns true..

eg.

In (1,2,3,4)

but in the features table i have just the id 4..

the select will still return

hmm. let me explain another way..


here is the car table

car_id            name
1                    206
2                     306

and the features

feature_id     car_id
1                      1
2                      1
2                      2

so if do like this:
SELECT * FROM CARS
INNER JOIN FEATURES ON FEATURE_CAR_ID = CAR_ID
WHERE FEATURE_ID In (1,2)
it will return car 1 and 2..
but it should return just car 1 (since car 2 has only feature id 2 and not 1)..

not sure if i was clear enough..


0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 19994206
that is clear!

what about this:

declare @test nvarchar(2000)
set @test = '1, 2, 3, 4'
SELECT c.car_id, c.name carname
FROM CARS c
INNER JOIN FEATURES f
  ON f.CAR_ID = c.CAR_ID
 AND f.FEATURE_ID In ( select value from dbo.parmstolist( @test) )
GROUP BY c.car_id, c.name
HAVING COUNT(*) = ( SELECT COUNT(distinct value) FROM dbo.parmstolist( @test) )


0
 
LVL 10

Author Comment

by:eguilherme
ID: 19994292
that worked just like i wanted!..

one last question.. about performance issues..
do you think that this will work fast even with many records ?

the In parameter will have at max like 30 items..
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19994300
assuming you have proper indexes on the fields/tables, yes, this will work fast.

glad I could help
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Screencast - Getting to Know the Pipeline
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question