MYSQL stored procedure debug help

Hi guys, getting error at line 8 near ' ) as TOTAL_CUSTOMERS FROM `summary` WHERE `storenumber' = 2
`at line 8
 

This is my 3rd attempt, and get the same error every time.

PART 1)  THIS IS MY SP, with CALL


DELIMITER $$
DROP PROCEDURE IF EXISTS `6_customer` $$
CREATE PROCEDURE `6_customer`(IN frst VARCHAR(255),
                              IN whst VARCHAR(255),
                              IN grst VARCHAR(255),
                              IN orst VARCHAR(255))
  BEGIN
        SET @comb_6 = CONCAT("


SELECT
       hour(transtime)                                                   AS HOURS,
       sum(cashsale + chargesale+newlayby) - (discount)                  AS VALUE,
       count(transref)                                                   AS CUSTOMERS,
             (SELECT COUNT((transref))",
             "FROM", frst,
             "WHERE", whst," ) as TOTAL_CUSTOMERS ",
"FROM", frst,
"WHERE", whst,
"GROUP BY", grst,
"ORDER BY", orst );

PREPARE C_A_6 FROM @comb_6;
EXECUTE C_A_6;
DEALLOCATE PREPARE C_A_6;

END $$
DELIMITER ;



CALL `6_customer`( " `summary` ",
                   " `storenumber` = 2
                        AND `transdate` BETWEEN '2010-02-01' AND '2010-02-01'
                        AND (`transtypn` =1 or 3 or 6 or 7)
                        AND (`cashsale` <> 0 or `chargesale`<>0 or `newlayby`<>0 or `discount`<>0) ",
                    " hour(`transtime`) ",
                    " hour(`transtime`) " );




PART 2) THIS PART IS THE QUERY BEFORE I PUT INTO STORED PROCEDURE ABOVE



SELECT
       hour(transtime)                                                   AS HOURS,
       sum(cashsale + chargesale+newlayby) - (discount)                  AS VALUE,
       count(transref)                                                   AS CUSTOMERS,
             (SELECT COUNT((transref))
             FROM summary
             WHERE storenumber = 2
             AND transdate BETWEEN '2010-02-01' AND '2010-02-01'
             AND (transtypn =1 or 3 or 6 or 7)
             AND (cashsale <> 0 or chargesale<>0 or newlayby<>0 or discount<>0)) as TOTAL_CUSTOMERS
FROM `summary`
WHERE `storenumber` = 2
AND `transdate` BETWEEN '2010-02-01' AND '2010-02-01'
AND (`transtypn` =1 or 3 or 6 or 7)
AND (`cashsale` <> 0 or `chargesale`<>0 or `newlayby`<>0 or `discount`<>0)
group by hour(`transtime`)
order by hour(`transtime`) ;
LVL 8
kingjelyAsked:
Who is Participating?
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.

flytox06Commented:
DELIMITER $$
DROP PROCEDURE IF EXISTS `6_customer` $$
CREATE PROCEDURE `6_customer`(IN frst VARCHAR(255),
                              IN whst VARCHAR(255),
                              IN grst VARCHAR(255),
                              IN orst VARCHAR(255))
  BEGIN
        SET @comb_6 = CONCAT("
SELECT
       hour(transtime)                                                   AS HOURS,
       sum(cashsale + chargesale+newlayby) - (discount)                  AS VALUE,
       count(transref)                                                   AS CUSTOMERS,
             (SELECT COUNT((transref)) FROM", frst, "WHERE", whst," ) as TOTAL_CUSTOMERS ", "
FROM", frst,
"WHERE", whst,
"GROUP BY", grst,
"ORDER BY", orst );

SELECT @comb_6;

PREPARE C_A_6 FROM @comb_6;
EXECUTE C_A_6;
DEALLOCATE PREPARE C_A_6;

END $$
DELIMITER ;


CALL `6_customer`( " `summary` ",
                   " `storenumber` = 2 AND `transdate` BETWEEN '2010-02-01' AND '2010-02-01' AND (`transtypn` =1 or 3 or 6 or 7) AND (`cashsale` <> 0 or `chargesale`<>0 or `newlayby`<>0 or `discount`<>0) ",
                    " hour(`transtime`) ",
                    " hour(`transtime`) " );
0
flytox06Commented:
That should work as pasted.

I think there were/are several different problems here:

- I've modified a bit you CONCAT part
- I've inlined the 2nd parameter in your call (remember you only have 255 chars available for this parameter, line return + spaces count !)
- do you really need to parameterize table name, where, group and order by ? can't you simply pass parameter values of dates, types, etc ?
- think about computing TOTAL_CUSTOMERS part BEFORE the returning select because this result is constant, so no need to computer it the way you do. Store result in a variable, select the variable in 2nd select.

Good luck !
0
kingjelyAuthor Commented:

Hi Flytox
I took out the 'SELECT @comb_6;'  as this didnt work.

- What exactly did you change, in teh concat? other then making it 1 line?
-  What does inlining change in the parameter.

- Could you give me an example, using my Procedure already, of where and how you would put the TOTAL_CUSTOMERS part, as it has the 'grst' and 'whst' parameters in it also?


Thanks heaps, for helping me

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

kingjelyAuthor Commented:

I dont understand why

This randomly spaced Call works,

CALL `6_customer`( " `summary` ",
                   " `storenumber` = 2
                        AND `transdate`
                        BETWEEN
                                 '2010-02-01' AND '2010-02-01'

                                     AND (`transtypn` =1 or 3 or 6 or 7)
                        AND (`cashsale` <> 0 or `chargesale`<>0 or `newlayby`<>0 or `discount`<>0) ",
                    " hour(`transtime`) ",
                    " hour(`transtime`) " )

;

BUT LIKE THIS

CALL `6_customer`( " `summary` ",
                   " `storenumber` = 2
                        AND `transdate` BETWEEN '2010-02-01' AND '2010-02-01'
                        AND (`transtypn` =1 or 3 or 6 or 7)
                        AND (`cashsale` <> 0 or `chargesale`<>0 or `newlayby`<>0 or `discount`<>0) ",
                    " hour(`transtime`) ",
                    " hour(`transtime`) " );

Gets error at line 8 ??????

what am i not understanding? =(
0
kingjelyAuthor Commented:
Is my problem that i have

VARCHAR(255) for the parameter fields?

Will i get an error if there is 50 shopnames being passed into VARCHAR(255)?
0
flytox06Commented:
yup, forgot to remove trace. is it working now ?

- I've merged FROM part with preceding one, I think that's all as far as I remember
- it changes that when your parameter is over 255 chars, it gets truncated, so you loose a part of this parameter which can lead to invalid syntax during execution. So, ensure it is big enough not to expose yourself to this issue.

Please read below the corrected stored procedure :

DELIMITER $$
DROP PROCEDURE IF EXISTS `6_customer` $$
CREATE PROCEDURE `6_customer`(IN frst VARCHAR(255),
                              IN whst VARCHAR(2000),
                              IN grst VARCHAR(255),
                              IN orst VARCHAR(255))
  BEGIN

SET @custcount = CONCAT("SELECT COUNT((transref)) INTO @totalcustomers FROM", frst, "WHERE", whst);
PREPARE stmtcount FROM @custcount;
EXECUTE stmtcount;
DEALLOCATE PREPARE stmtcount;

SET @comb_6 = CONCAT("
SELECT
       hour(transtime)                                                   AS HOURS,
       sum(cashsale + chargesale+newlayby) - (discount)                  AS VALUE,
       count(transref)                                                   AS CUSTOMERS,
       @totalcustomers as TOTAL_CUSTOMERS ", "
FROM", frst,
"WHERE", whst,
"GROUP BY", grst,
"ORDER BY", orst );

PREPARE C_A_6 FROM @comb_6;
EXECUTE C_A_6;
DEALLOCATE PREPARE C_A_6;

END $$
DELIMITER ;


CALL `6_customer`( " `summary` ",
                   " `storenumber` = 2 AND `transdate` BETWEEN '2010-02-01' AND '2010-02-01' AND `transtypn` IN (1,3,6,7) AND (`cashsale` <> 0 or `chargesale`<>0 or `newlayby`<>0 or `discount`<>0) ",
                    " hour(`transtime`) ",
                    " hour(`transtime`) " );




I've increased the parameter length, added the preceding total count and corrected your where clause where transtypn filter was wrong.
0

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:
Wow, that looks alot harder and lots more work !

Will it make it faster to do seperate prepare statements, or what is the point in putting in seperate prepare statements.
0
flytox06Commented:
1) you avoid callint the statement for every line you return (e.g you select 1000 lines, you count 1000 times the grand total)
2) you get better view when reading the code
0
kingjelyAuthor Commented:
I've only learn't about Stored procedures in the last week, and i have a stack to do by monday.

I'm going to try to learn how you ahve done it, but first, I have to get as much done as possible, with what i Know, with is next to Jack. Hehe Thanks heaps For your help.
I wish i was as smart as all you guys .damit
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.