CODEWITHSUNDEEP
sqlsql-serverstored-proceduresif-statementcase
user2363025
user2363025

Reputation: 6505

dynamic where clause in one 'AND' of stored procedure

I have a really big stored Procedure which I cannot share but I am only having trouble with implementing a dynamic where clause, which already has 7 ANDs. Within the last 'And' of the WHERE clause, I need to check a parameter passed to the stored procedure and construct the 'And' accordingly. Basically, a user can pass in either Buyer or seller or an empty string (indicating to use both), to the stored procedure. Algorithm needed:

SELECT blah blah blah 
FROM multiple joins, left outer joins blah
WHERE 
(1st clause) AND
(2nd clause) AND 
(3rd clause) AND 
(4th clause) AND 
(5th clause) AND 
(6th clause) AND 
(7th clause) AND
(need to check paramater passed in and fill in this and accordingly....
Basically in JS terms:
if (parameter ='buyer'){
 B.S_FNAME LIKE '%' + @Name + '%' OR 
 B.S_SNAME LIKE '%' + @Name + '%'
}
else if (parameter = 'seller'){
 S.S_FNAME LIKE '%' + @Name + '%' OR
 S.S_SNAME LIKE '%' + @Name + '%'
}
else{
 B.S_FNAME LIKE '%' + @Name + '%' OR 
 S.S_FNAME LIKE '%' + @Name + '%' OR 
 B.S_SNAME LIKE '%' + @Name + '%' OR 
 S.S_SNAME LIKE '%' + @Name + '%'
}
 )

My attempt was as follows where @CliType is the parameter passed in:

AND (CASE 
        WHEN @CliType  = 'Buyer' THEN  
        B.S_FNAME LIKE '%' + @Name + '%' OR 
        B.S_SNAME LIKE '%' + @Name + '%'
        WHEN @CliType  = 'Seller' THEN 
        S.S_FNAME LIKE '%' + @Name + '%' OR
        S.S_SNAME LIKE '%' + @Name + '%'
        WHEN @CliType = '' THEN 
        B.S_FNAME LIKE '%' + @Name + '%' OR 
        S.S_FNAME LIKE '%' + @Name + '%' OR 
        B.S_SNAME LIKE '%' + @Name + '%' OR 
        S.S_SNAME LIKE '%' + @Name + '%'
        END)

But this gives me error lines under the first LIKE and the second WHEN. I'm using sql server 2008 and have not much experience with it. Any help appreciated

Upvotes: 0

Views: 805

Answers (3)

Andrew
Andrew

Reputation: 8693

You can parse the parameter and build a string variable that you plug into your select statement.

if  @CliType  = 'Buyer' 
  THEN @WhereVariable = B.S_FNAME LIKE '''%''' + @Name + '''%''' OR...'
else if @CliType = 'Seller' then ...

Then just plug that into a string that ends up being your full query, and execute it with sp_executesql

Upvotes: 0

Gouri Shankar Aechoor
Gouri Shankar Aechoor

Reputation: 1581

Please try this, hope its useful.

DECLARE @CliType VARCHAR(100);

DECLARE @Name VARCHAR(100);

DECLARE @TableBuyer TABLE (ID INT,
                          S_FNAME VARCHAR(100),
                          S_SNAME VARCHAR(100));

DECLARE @TableSeller TABLE (ID INT,
                           S_FNAME VARCHAR(100),
                           S_SNAME VARCHAR(100));

--Assign your input parameters here
SET @CliType = 'Buyer' --NULL
SET @Name = 'Daniella'

INSERT INTO @TableBuyer
SELECT '1','Bryan', 'Greenberg' UNION ALL
SELECT '2','Channing', 'Tatum' UNION ALL
SELECT '3','Paul', 'William' UNION ALL
SELECT '4','Eric', 'Bana' UNION ALL
SELECT '5','James', 'Lafferty' UNION ALL
SELECT '6','Wentworth', 'Miller'

INSERT INTO @TableSeller
SELECT '1','Dianna', 'Agron' UNION ALL
SELECT '2','Malin', 'Akerman' UNION ALL
SELECT '3','Christina', 'Aguilera' UNION ALL
SELECT '4','Jessica', 'Alba' UNION ALL
SELECT '5','Krista', 'Allen' UNION ALL
SELECT '6','Daniella', 'Alonso'


SELECT b.ID,b.S_FNAME,b.S_SNAME,s.ID,s.S_FNAME,s.S_SNAME
FROM @TableBuyer b
JOIN @TableSeller s
ON b.ID=s.ID
WHERE (@CliType = 'Buyer'
 AND (B.S_FNAME LIKE '%' + @Name + '%'
   OR B.S_SNAME LIKE '%' + @Name + '%'))
  OR (@CliType = 'Seller'
 AND (S.S_FNAME LIKE '%' + @Name + '%'
   OR S.S_SNAME LIKE '%' + @Name + '%'))
  OR (ISNULL(@CliType, '') = ''
 AND (B.S_FNAME LIKE '%' + @Name + '%'
   OR S.S_FNAME LIKE '%' + @Name + '%'
   OR B.S_SNAME LIKE '%' + @Name + '%'
   OR S.S_SNAME LIKE '%' + @Name + '%'));

Upvotes: 2

TMNT2014
TMNT2014

Reputation: 2130

Thats not how the Case Statement works. The basic idea is to use a similar syntax for each condition you are trying to check.

AND (CASE 
    WHEN @CliType  = 'Buyer' THEN  
    B.S_FNAME WHEN @CliType  = 'Seller' THEN 
    S.S_FNAME WHEN @CliType = '' THEN 
    B.S_FNAME END LIKE '%' + @Name + '%' )
OR..

Upvotes: 0

Related Questions