Multiple parameter values logic

Question

I have a simple select and two parameters.

@first
@second

SELECT ProductID, ProductName, ProductType, ProductLevel
FROM ProductTable

When I execute the procedure if both parameters have no values then I need this part to be executed:

WHERE ProductType = 1 AND ProductLevel = 1

IF I have value of first parameter then :

WHERE ProductType = 2 AND ProductName LIKE  @first + '%'

IF I have value of second parameter then :

WHERE ProductType =3 AND ProductName LIKE  @second + '%'

Can someone help me to implement this logic and create valid stored procedure ?

Yara · Accepted Answer · 2018-07-27 11:40:28Z

4

If I understand you right then you need the next 'WHERE' statement:

WHERE 
    (@first IS NULL AND @second IS NULL AND ProductType = 1 AND ProductLevel = 1)
    OR
    (@first is NOT NULL AND ProductType = 2 AND ProductName LIKE @first + '%')
    OR
    (@second is NOT NULL AND ProductType = 3 AND ProductName LIKE @second + '%')

answered Jul 27, 2018 at 11:40

Yara

4,5856 gold badges46 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

freej17 Over a year ago

When I declare parameter and execute procedure it still expects parameter who are not supplied if I do not write any values to them :/

Yara Over a year ago

What if you make your parameter as optional? @first VARCHAR(5) = NULL

Rohan Sable · Accepted Answer · 2018-07-27 11:40:31Z

1

You Should try IN Format:

DROP PROCEDURE IF EXISTS products;

DELIMITER $$

CREATE PROCEDURE products(
IN p_ProductName,
IN p_ProductType,
IN p_ProductLevel
)

BEGIN

   SELECT * FROM ProductTable WHERE ProductName LIKE '%p_ProductName%', ProductType LIKE '%p_ProductType%' , ProductLevel LIKE '%p_ProductLevel%';

END$$

answered Jul 27, 2018 at 11:40

Rohan Sable

256 bronze badges

Comments

Rence Abishek · Accepted Answer · 2018-07-30 17:39:15Z

1

Adding Another way,

declare @whereCondition varchar(100)

if(@first='' && @second='')
 @whereCondition=ProductType = 1 AND ProductLevel = 1
else if(@first<>'' && @second='')
 @whereCondition=ProductType = 2 AND ProductName LIKE  @first + '%'
else if(@first='' && @second<>'')
 @whereCondition=ProductType =3 AND ProductName LIKE  @second + '%'

exec('SELECT ProductID, ProductName, ProductType, ProductLevel
FROM ProductTable where=@whereCondition')

if the logic seems bigger, we can achieve by this way.

answered Jul 30, 2018 at 17:39

Rence Abishek

4681 gold badge5 silver badges16 bronze badges

Collectives™ on Stack Overflow

Multiple parameter values logic

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related