Mysql Stored procedure - Multiple numeric parameters


Hi guys, Day 2 of learning stored procedures, with parameters!
Okay, i have this

DELIMITER $$
DROP PROCEDURE IF EXISTS `SBC` $$
CREATE PROCEDURE `SBC`(IN store INT(20),
                       IN brand INT(20),
                       IN dept  INT(20),
                       IN subd  INT(20),
                       IN prod  INT(20),
                       IN cat   INT(20),
                       IN seas  INT(20))
BEGIN

        SELECT `storename`, `brandname`, `deptname`, `subdname`, `prodname`, `catename`, `seasname`
        FROM   `history`
        WHERE  `storenumber` LIKE CONCAT('%',store,'%') OR
               `brandnumber` LIKE CONCAT('%',brand,'%') OR
               `deptcode`  LIKE CONCAT('%',dept,'%')  OR
               `subdcode`  LIKE CONCAT('%',subd,'%')   OR
               `prodcode`  LIKE CONCAT('%',cat,'%')   OR
               `seascode`  LIKE CONCAT('%',seas,'%');

END $$
DELIMITER;

call `SBC`('9','2','1','1','1','1','1');


What I want to do is have a list of the parameters, which I have done. and then
beable to
call `SBC`('2','2','1','1','1','1','1');
which I WANT TO RETURN

storename   brandname  deptname  subdname   prodname  catename  seasname
belmont       Billabong     clothing     clothing       mens        boardies    summer2009      

INSTEAD, I get ALL records for each field, so It's not restricting the numbers i put into CALL

its like it's doing
all `SBC`('%','%','%','%','%','%','%');

eg;
storename    brandname  deptname  subdname    prodname  catename  seasname
All16 stores   50 brands    100depts   100subdeps  

I hope this makes sence
Thankyou.
LVL 8
kingjelyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Om PrakashCommented:
If you want to dislpay only distinct records from the query then use the following query:
Also, if the fields are numeric then you can compare directly

 SELECT distinct `storename`, `brandname`, `deptname`, `subdname`, `prodname`, `catename`, `seasname`
        FROM   `history`
        WHERE  `storenumber` = store OR
               `brandnumber` = brand  OR
               `deptcode`  = dept OR
               `subdcode` = subd OR
               `prodcode`  = cat  OR
               `seascode`  = seas ;

Open in new window

kingjelyAuthor Commented:
HI om i got the same result
DELIMITER $$
DROP PROCEDURE IF EXISTS `SBC` $$
CREATE PROCEDURE `SBC`(IN store INT(20),
                       IN brand INT(20),
                       IN dept  INT(20),
                       IN subd  INT(20),
                       IN prod  INT(20),
                       IN cat   INT(20),
                       IN seas  INT(20))
BEGIN

        SELECT `storename`, `brandname`, `deptname`, `subdname`, `prodname`, `catename`, `seasname`
        FROM   `history`
        WHERE  `storenumber` = store OR
               `brandnumber` = brand OR
               `deptcode`    = dept  OR
               `subdcode`    = subd  OR
               `prodcode`    = cat   OR
               `seascode`    = seas;

END $$
DELIMITER;

call `SBC`('2','2','1','1','1','1','1');

still returns every record
as if i have entered CALL as this
call `SBC`('%','%','%','%','%','%','%');
Om PrakashCommented:
I think you need results only when all conditions are true for parameters you passed in procedure. Please check the modified code:

 SELECT distinct `storename`, `brandname`, `deptname`, `subdname`, `prodname`, `catename`, `seasname`
        FROM   `history`
        WHERE  `storenumber` = store AND
               `brandnumber` = brand  AND
               `deptcode`  = dept AND
               `subdcode` = subd AND
               `prodcode`  = cat  AND
               `seascode`  = seas ;

Open in new window

10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

kingjelyAuthor Commented:

I'm finding it hard to test
Is there anyway to pass everything in some parameters, like a string you can pass '%'

What I want to do is enter

call `SBC`('2','2','ALL','ALL','ALL','ALL','ALL');
(what can i enter for ALL if the parameter is numeric)

to return

storename   brandname  deptname  subdname   prodname  catename  seasname
belmont       Billabong     ALL          ALL            ALL          ALL           ALL

not literally 'ALL' but '%' as in 'I don't care' return everything
eg returns
storename   brandname  deptname  subdname   prodname  catename  seasname
belmont       Billabong     clothing     clothing       mens       boardies     S2010
belmont       Billabong     clothing     clothing       ladies       teeshirt      S2009
belmont       Billabong     accesso    accesso       kids         hat           W2010
belmont       Billabong     accesso    accesso       girls         backpack  H2010
ect...
SharathData EngineerCommented:
Can you try like this?
SELECT DISTINCT CASE 
                  WHEN store = 'ALL' THEN 'ALL' 
                  ELSE `storename` 
                END AS storename, 
                CASE 
                  WHEN brand = 'ALL' THEN 'ALL' 
                  ELSE `brandname` 
                END AS brandname, 
                CASE 
                  WHEN dept = 'ALL' THEN 'ALL' 
                  ELSE `deptname` 
                END AS deptname, 
                CASE 
                  WHEN subd = 'ALL' THEN 'ALL' 
                  ELSE `subdname` 
                END AS subdname, 
                CASE 
                  WHEN cat = 'ALL' THEN 'ALL' 
                  ELSE `prodname` 
                END AS prodname, 
                CASE 
                  WHEN cat = 'ALL' THEN 'ALL' 
                  ELSE `catename` 
                END AS catname, 
                CASE 
                  WHEN seas = 'ALL' THEN 'ALL' 
                  ELSE `seasname` 
                END AS seasname 
FROM   `history` 
WHERE  `storenumber` = CASE 
                         WHEN store = 'ALL' THEN `storenumber` 
                         ELSE store 
                       END 
       AND `brandnumber` = CASE 
                             WHEN brand = 'ALL' THEN `brandnumber` 
                             ELSE brand 
                           END 
       AND `deptcode` = CASE 
                          WHEN dept = 'ALL' THEN `deptcode` 
                          ELSE dept 
                        END 
       AND `subdcode` = CASE 
                          WHEN subd = 'ALL' THEN `subdcode` 
                          ELSE subd 
                        END 
       AND `prodcode` = CASE 
                          WHEN cat = 'ALL' THEN `prodcode` 
                          ELSE cat 
                        END 
       AND `seascode` = CASE 
                          WHEN seas = 'ALL' THEN `seascode` 
                          ELSE seas 
                        END;

Open in new window

kingjelyAuthor Commented:
Hi Sharath do i need to use

CALL to call the select, from the SP?
kingjelyAuthor Commented:
I get

! Unknown column 'store' in 'field list'
SharathData EngineerCommented:
Create the SP with the query provided and call SP.
kingjelyAuthor Commented:
Hi Sharath,
What you did has worked, but is not what i need,
Sorry!
Could you please, re-read the last part of my post above your code?

Sorry , thanks!
kyadavCommented:
Using IF condition you can create query and exceute it as show in code
 DECLARE QueryString VARCHAR(255);

 SET @QueryString = CONCAT('SELECT `storename`, `brandname`, `deptname`, `subdname`, `prodname`, `catename`, `seasname` FROM   `history` WHERE  1=1 ');

 # Condition for dept 
 # If value of dept is not number then it will be zero always	  
 IF dept  !=0 THEN
	  SET @QueryString = CONCAT(@QueryString, ' AND deptcode= ', dept );
END IF;	 

# 
# You can put condition for all input paramater
#
#

SET @QueryString = CONCAT(@QueryString, ';');
	  
#Execute query 
PREPARE stmt FROM @QueryString;
EXECUTE stmt;	

Open in new window

SharathData EngineerCommented:
Can you provide some sample data from your table and expected result.
kingjelyAuthor Commented:

Expected results are;


not literally 'ALL' but '%' as in 'I don't care' return everything
eg returns
storename   brandname  deptname  subdname   prodname  catename  seasname
belmont       Billabong     clothing     clothing       mens       boardies     S2010
belmont       Billabong     clothing     clothing       ladies       teeshirt      S2009
belmont       Billabong     accesso    accesso       kids         hat           W2010
belmont       Billabong     accesso    accesso       girls         backpack  H2010

I'm Not sure how to give sample data
kyadavCommented:
Hi kingjely

Use this store procedure code
and call it like this :  call `SBC`('2','2','%','%','%','%','%');

You will get expected result.


Ragards

DELIMITER $$
DROP PROCEDURE IF EXISTS `SBC` $$
CREATE PROCEDURE `SBC`(IN store INT(20),
                       IN brand INT(20),
                       IN dept  INT(20),
                       IN subd  INT(20),
                       IN prod  INT(20),
                       IN cat   INT(20),
                       IN seas  INT(20))
BEGIN

	 DECLARE QueryString VARCHAR(255);

	 SET @QueryString = CONCAT('SELECT `storename`, `brandname`, `deptname`, `subdname`, `prodname`, `catename`, `seasname` FROM   `history` WHERE  1=1 ');

	 # Condition for dept 
	 # If value of dept is not number then it will be zero always	  
	 IF store !=0 THEN
		  SET @QueryString = CONCAT(@QueryString, ' AND `storenumber`= ', store );
	 END IF;	 
	 IF brand !=0 THEN
		  SET @QueryString = CONCAT(@QueryString, ' AND `brandnumber` = ', brand );
	 END IF;
	 IF dept !=0 THEN
		  SET @QueryString = CONCAT(@QueryString, ' AND `deptcode`= ', dept );
	 END IF;	 
	 IF subd !=0 THEN
		  SET @QueryString = CONCAT(@QueryString, ' AND `subdcode`= ', subd );
	 END IF;
	 IF prod !=0 THEN
		  SET @QueryString = CONCAT(@QueryString, ' AND `prodcode`= ', prod );
	 END IF;	 
	 IF cat !=0 THEN
		  SET @QueryString = CONCAT(@QueryString, ' AND `catcode`= ', cat );
	 END IF;
	 IF seas !=0 THEN
		  SET @QueryString = CONCAT(@QueryString, ' AND `seascode`= ', seas );
	 END IF;
	 
	SET @QueryString = CONCAT(@QueryString, ';');
		  
	#Execute query 
	PREPARE stmt FROM @QueryString;
	EXECUTE stmt; 

END $$
DELIMITER;

Open in new window

kingjelyAuthor Commented:
WOW kyadav

Way to go champ !

I will award you full points if you can explain a bit of how you came to do this and also
when to use PREPARE stmt compared to STORED procedure.

Im way new to this !

Well done though !!
kingjelyAuthor Commented:
What i mean is could you write # comments on it explaining what you have done and how please so i can try to understand.

Thanks!
kyadavCommented:
I have added more comment in store procedure.
And if you want to create dynamic query then use can create query string in store procedure
and using  prepare statement you can execute it.

Hope it will helpful to understand.
DELIMITER $$
DROP PROCEDURE IF EXISTS `SBC` $$
CREATE PROCEDURE `SBC`(IN store INT(20),
                       IN brand INT(20),
                       IN dept  INT(20),
                       IN subd  INT(20),
                       IN prod  INT(20),
                       IN cat   INT(20),
                       IN seas  INT(20))
BEGIN
	# Declare variable for query
	 DECLARE QueryString VARCHAR(255);
  
	 SET @QueryString = CONCAT('SELECT `storename`, `brandname`, `deptname`, `subdname`, `prodname`, `catename`, `seasname` FROM   `history` WHERE  1=1 ');

	 # Below we are going to add 'AND' condition for coulmn for which you have numeric IN parameter values, other wise skip it
	 # If you are passing '%' as IN parameter then it will consider as zero and will skip adding AND condtion for it.
	 # So by checking condition != 0 you can add AND conditon in query.
	 # 
	 # For call `SBC`('2','2','%','%','%','%','%');  it will create query as fallows :
	 # 
	 #  SELECT `storename`, `brandname`, `deptname`, `subdname`, `prodname`, `catename`, `seasname` FROM   `history` WHERE  1=1  AND `storenumber`= 2 AND `brandnumber` = 2;
	 #  And then prapare statement from qurey and execute it. 
	   
	 IF store !=0 THEN
		  SET @QueryString = CONCAT(@QueryString, ' AND `storenumber`= ', store );
	 END IF;	 
	 IF brand !=0 THEN
		  SET @QueryString = CONCAT(@QueryString, ' AND `brandnumber` = ', brand );
	 END IF;
	 IF dept !=0 THEN
		  SET @QueryString = CONCAT(@QueryString, ' AND `deptcode`= ', dept );
	 END IF;	 
	 IF subd !=0 THEN
		  SET @QueryString = CONCAT(@QueryString, ' AND `subdcode`= ', subd );
	 END IF;
	 IF prod !=0 THEN
		  SET @QueryString = CONCAT(@QueryString, ' AND `prodcode`= ', prod );
	 END IF;	 
	 IF cat !=0 THEN
		  SET @QueryString = CONCAT(@QueryString, ' AND `catcode`= ', cat );
	 END IF;
	 IF seas !=0 THEN
		  SET @QueryString = CONCAT(@QueryString, ' AND `seascode`= ', seas );
	 END IF;
	 
	SET @QueryString = CONCAT(@QueryString, ';');
		  
	#Prepare statement from query
	PREPARE stmt FROM @QueryString;
	#Execute query 
	EXECUTE stmt; 

END $$
DELIMITER ;

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kingjelyAuthor Commented:

You're a wizard. =D

To do with dynamic parameters could you check my question here..

http://www.experts-exchange.com/Database/MySQL/Q_26022686.html#a31843653

THanks Heaps !
kingjelyAuthor Commented:
Thanks VERY much !
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.