Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

Mysql Stored proc pass all values help

Avatar of kingjely
kingjelyFlag for Australia asked on
DatabasesMySQL ServerSQL
20 Comments1 Solution470 ViewsLast Modified:
Hi guys.

I have attached a stored procedure.
For store, brand, depart, subd, product, categ fabric, season and purchase parameters, If i want ALL VALUES,
Is there an easier faster way then, using REGEXP and passing it eg '[-0-9]'  in the call for everything.

is there a way to just NOT include the parameter, so everything is passed.

Like if BRAND = '' then pass it everything..

Hope that makes sense..
Kj

DELIMITER $$
DROP PROCEDURE IF EXISTS `BW` $$
CREATE PROCEDURE `BW`(IN STORE VARCHAR(2555),
                      IN BRAND VARCHAR(2555),
                      IN DEPART VARCHAR(2555),
                      IN SUBD VARCHAR(2555),
                      IN PRODUCT VARCHAR(2555),
                      IN CATEG VARCHAR(2555),
                      IN FABRIC VARCHAR(2555),
                      IN SEASON VARCHAR(2000),
                      IN PURCHASE VARCHAR(2000),
                      IN STYLE VARCHAR(255),
                      IN STARTDATE VARCHAR(255),
                      IN ENDDATE VARCHAR(255))

BEGIN

    SET @bws = CONCAT("

Select s.storename,
       s.storenumber,
       s.brandname,
       s.brandnumber,
       s.deptname,
       s.deptcode,
       s.subdname,
       s.subdcode,
       s.prodname,
       s.prodcode,
       s.catename,
       s.catecode,
       s.fabricname,
       s.fabricno,
       s.seasname,
       s.seascode,
       s.purchflagdescription,
       s.purchflagcode,
       s.stylecode,
       s.description,
       s.datefirstrecv,
       s.datelastrecv,
       sum(h.quantity) as SOLD,
       s.datelastsold,


(select ifnull(sum(h.quantity),0)
from history h
where (h.stockcode = s.stockcode
and h.storenumber = s.storenumber)
and h.subtranstype = 6
and s.storenumber IN   (",STORE,")
and s.brandnumber IN   (",BRAND,")
and s.deptcode REGEXP    ",DEPART,"
and s.subdcode REGEXP    ",SUBD,"
and s.prodcode REGEXP    ",PRODUCT,"
and s.catecode REGEXP    ",CATEG,"
and s.fabricno REGEXP    ",FABRIC,"
and s.seascode REGEXP    ",SEASON,"
and s.purchflagcode REGEXP   ",PURCHASE,"
and s.stylecode LIKE     ",STYLE,"
and (s.datefirstrecv or s.datelastrecv or s.datelastsold) is not null
and  h.transdate between ",STARTDATE," and ",ENDDATE," ) as QTYRECV,


(select ifnull(sum(h.quantity),0)
from history h
where (h.stockcode = s.stockcode
and h.storenumber = s.storenumber)
and h.subtranstype < '4'
and s.storenumber IN   (",STORE,")
and s.brandnumber IN   (",BRAND,")
and s.deptcode REGEXP    ",DEPART,"
and s.subdcode REGEXP    ",SUBD,"
and s.prodcode REGEXP    ",PRODUCT,"
and s.catecode REGEXP    ",CATEG,"
and s.fabricno REGEXP    ",FABRIC,"
and s.seascode REGEXP    ",SEASON,"
and s.purchflagcode REGEXP   ",PURCHASE,"
and s.stylecode LIKE     ",STYLE,"
and (s.datefirstrecv or s.datelastrecv or s.datelastsold) is not null
and  h.transdate between ",STARTDATE," and ",ENDDATE," ) ONHAND




from history h INNER JOIN styleperformance s
ON (h.storenumber=s.storenumber
and h.stockcode=s.stockcode)
and h.subtranstype < '4'
and s.storenumber IN   (",STORE,")
and s.brandnumber IN   (",BRAND,")
and s.deptcode RLIKE    ",DEPART,"
and s.subdcode RLIKE    ",SUBD,"
and s.prodcode RLIKE    ",PRODUCT,"
and s.catecode RLIKE    ",CATEG,"
and s.fabricno RLIKE    ",FABRIC,"
and s.seascode RLIKE    ",SEASON,"
and s.purchflagcode RLIKE   ",PURCHASE,"
and s.stylecode LIKE     ",STYLE,"
and (s.datefirstrecv or s.datelastrecv or s.datelastsold) is not null
and  h.transdate between ",STARTDATE," and ",ENDDATE,"
group by s.storenumber, s.stockcode
ORDER BY SOLD DESC
LIMIT 20  ");

#SELECT @bws;
PREPARE b_w_s from @bws;
EXECUTE b_w_s;
DEALLOCATE PREPARE b_w_s;

END $$
DELIMITER;

CALL `BW`("1,2,9,10","1,2,3,4,5,6,7,8,9,10,11,12,13,14,15","'[-0-9]'","'[-0-9]'","'[-0-9]'","'[-0-9]'","'[-0-9a-z]'","'[-0-9a-z]'","'[-0-9a-z]'","'%'","'2010-06-01'","'2010-07-03'")