CODEWITHSUNDEEP
sql-servert-sqlselectif-statement
lleoun
lleoun

Reputation: 477

SQL select procedure : how to select a range

I have this procedure: 

 USE myDataBase

 DECLARE 
  @id INT = NULL
 ,@name VARCHAR(250)= NULL
 ,@id2 INT = NULL
 ,@flag INT =NULL

 SELECT *
    FROM    dbo.table1  INNER JOIN
            dbo.table2 ON id = id2 

   WHERE (@id IS NULL OR @id = id)
   AND (@name IS NULL OR @name = name)
   AND (@id2 IS NULL OR @id2 = id2)

Depending on what @flag contains, I need @id2 in where clause to be in a centain range.

Meaning

if @flag = 2 --@flag can contain any value from 1 to 12

I need this line:

AND (@id2 IS NULL OR @id2 = id2)

To be: 

AND (@id2 IS NULL OR @id2 IN (61,62,63))

Also:

if @flag = 4 --@flag can contain any value from 1 to 12

I need this line:

AND (@id2 IS NULL OR @id2 = id2)

To be: 

AND (@id2 IS NULL OR @id2 IN (74,75,76))

And so on..

How can I do that?

I know is a stupid question, but I cannot make it work :(

Thanks

Upvotes: 1

Views: 57

Answers (2)

Kevin Hogg
Kevin Hogg

Reputation: 1781

Create a temporary table variable, and query using the contents of that variable.

DECLARE @IdList TABLE
(
    Id INT,
)

-- Populate temporary table variable with required Id's depending on @flag value
IF @flag = 1
BEGIN
    INSERT INTO @IdList VALUES (@id2)
END

IF @flag = 2
BEGIN
    INSERT INTO @IdList VALUES (61), (62), (63)
END

IF @flag = 4
BEGIN
    INSERT INTO @IdList VALUES (74), (75), (76)
END

-- Code for other flag values within 1-12


-- Perform query to get results
IF (SELECT COUNT(*) FROM @Temp) = 0
BEGIN
    -- No values provided for matching
     SELECT *
       FROM dbo.table1
            INNER JOIN dbo.table2 ON id = id2
      WHERE (@id IS NULL OR @id = id)
        AND (@name IS NULL OR @name = name)
END
ELSE
BEGIN
     -- One or more values provided for matching
     SELECT *
       FROM dbo.table1
            INNER JOIN dbo.table2 ON id = id2
                   AND id2 IN (SELECT Id FROM @IdList)
      WHERE (@id IS NULL OR @id = id)
        AND (@name IS NULL OR @name = name)
END

It's not clear how you're handling @id2 = NULL so the final IF clause is to separate the two.

Upvotes: 0

Paul Michaels
Paul Michaels

Reputation: 16705

How about:

AND ((@flag = 2 AND @id2 IS NULL OR @id2 IN (61,62,63)) OR
    (@id2 IS NULL OR @id2 = id2) )

EDIT:

AND (((@flag >= 1 AND @flag <=12) AND @id2 IS NULL OR @id2 IN (61,62,63)) OR
    (@id2 IS NULL OR @id2 = id2) )

Upvotes: 1

Related Questions