February 10, 2011 at 6:01 pm
I have a proc something like this:
CREATE myProc
AS
SELECT *
FROM Something
WHERE SomethingElse IN ('A','B')
I'd like to make the IN statement dynamic. I don't want to use dynamic SQL.
I'm contemplating something like this:
CREATE TABLE TEST
(
ID int,
Description varchar(50),
Member varchar(10)
)
INSERT INTO TEST
SELECT 1, 'A & B','A' UNION
SELECT 1, 'A & B','B' UNION
SELECT 2, 'A','A' UNION
SELECT 3, 'A, B & C','A' UNION
SELECT 3, 'A, B & C','B' UNION
SELECT 3, 'A, B & C','C'
SELECT * FROM TEST
DROP TABLE TEST
Then my proc would be
CREATE myProc @myID int
AS
SELECT *
FROM Something
WHERE SomethingElse IN (SELECT Member FROM TEST WHERE ID = @myID)
Currently there would be 3 records in my table. Given the data set I don't think there could be more than 10.
Am I heading down the wrong path? Is there a better way that is fairly simple?
February 23, 2011 at 6:48 am
Without knowing the reason you need a dynamic IN operation, your solution to avoid Dynamic SQL is fine. That being said, the creation / insertion / deltion of the table might be more costly than using dynamic SQL to begin with.
Also, the IN operator is really the "poor person's JOIN", so if you are able to rewrite it into a JOIN statement, you might be able to avoid the IN operation outright.
--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply