Create MySQL procedures

Question

I am currently trying to create a stored procedure on my MySQL stored on Google Cloud Platform.

The SQL is correct seeing that I can create the procedure locally, but I can't figure out why it won't work from the command line:

mysql> CREATE PROCEDURE helpme
    -> (
    ->  @cid varchar(4)
    -> )
    -> AS
    -> DECLARE @res_cnt INT
    -> DECLARE @name CHAR(10)
    ->
    -> SELECT @res_cnt = COUNT(*) FROM dbo.TripGuides WHERE GuideNum = @cid
    -> SELECT @name = LastName FROM dbo.Guide WHERE GuideNum = @cid
    -> PRINT @name + ' has ' +  CAST (@res_cnt AS VARCHAR(10))+' guides.';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@cid varchar(4)
)
AS
DECLARE @res_cnt INT
DECLARE @name CHAR(10)

SELECT @res_cn' at line 3
mysql>

I've tried a few different things thank I have bumped into. When declaring @cid I tried both

@cid CHAR(4)
@cid VARCHAR(4)

resulting in the same error being thrown.

Does this answer your question? [MySQL create stored procedure syntax with delimiter](https://stackoverflow.com/questions/15786240/mysql-create-stored-procedure-syntax-with-delimiter) — danblack, Apr 24 '20 at 04:57
also `@cid` is a user variable, which are different from procedures arguments and `DECLARE` variables. — danblack, Apr 24 '20 at 04:58
@ danblack No, unless you are saying I have to add DELIMITER to my call. I also want @cid to be a user defined variable. — dapi, Apr 24 '20 at 05:08
you are getting syntax errors because `SELECT` statements aren't ending with`;`. Because `DELIMITER` isn't set you will get different syntax errors as the mysql client treats that as the end of the `CREATE PROCEDURE`. after you resolve that you will get the UDV errors, because they aren't the same as parameters or local variables. Also stored procedures and rarely offer any useful benefit, and are hard to debug. Avoid whenever possible. — danblack, Apr 24 '20 at 05:20
@danblack Oh, I took out all the ; since it would auto run once it hit that location. But the delimiters will temporally stop that from happening. Thank you that's really good to know. I also feel like I'm using the wrong syntax all together for defining variables. instead of DECLARE #res_cnt INT should I simple be placing set #res_cnt = COUNT(*) FROM TripGuides WHERE GuideNum = #cid; — dapi, Apr 24 '20 at 05:44
Please treat all # as @, stack overflow wouldn't allow me to place so many in one comment. — dapi, Apr 24 '20 at 05:45
Please explain why you use SQL server syntax whereas in subj and tags you mark the question as MySQL-related? — Akina, Apr 24 '20 at 06:03
@Akina well sir, its obvious that I'm not very versed in the topic. How ever I am trying to figure it out. So their a different syntax for SQL server and MYSQL? — dapi, Apr 24 '20 at 06:06
While it's fun to learn how to do new stuff, and that's fine, I don't actually see anything 'procedural' here — Strawberry, Apr 24 '20 at 07:31

Akina · Answer 1 · 2020-04-24T07:10:33.880

1

For MySQL use

CREATE PROCEDURE helpme ( cid VARCHAR(4) )
SELECT CONCAT( ( SELECT LastName 
                 FROM Guide 
                 WHERE GuideNum = cid ), 
               ' has ', 
               ( SELECT COUNT(*) 
                 FROM TripGuides 
                 WHERE GuideNum = cid ), 
               ' guides.'
             ) AS message;

Just out of curiosity how would I go about declaring res_cnt and name as to the original SQL call i did before?

CREATE PROCEDURE helpme ( cid VARCHAR(4) )
BEGIN
    DECLARE res_cnt INT;
    DECLARE `name` CHAR(10);

    SELECT COUNT(*) INTO res_cnt FROM TripGuides WHERE GuideNum = cid;
    SELECT LastName INTO `name` FROM Guide WHERE GuideNum = cid;
    SELECT CONCAT( `name`, ' has ', res_cnt, ' guides.' ) AS output_message;
END

And do not forget about DELIMITER re-assign in that case.

edited Apr 24 '20 at 07:10

answered Apr 24 '20 at 06:07

Akina

39,301
5
14
25

So that did work but not till I removed the AS. But at the same time I'm not 100% sure why. So CONCAT( ) will allow me to add two SELECT calls together. Then the "AS message" will print what ever is in the CONCAT( )? – dapi Apr 24 '20 at 06:16
@dapi *that did work but not till I removed the AS* Yes, `AS` was excess - removed. *Then the "AS message" will print what ever is in the CONCAT( )?* No, `AS message` is added to make it easier to access it in the code (you may use the field name alias `message` instead of indexing number reference). The alias may be any valid, used `message` name is random. – Akina Apr 24 '20 at 06:21
Ok, I think I got it. Thank you for your time. – dapi Apr 24 '20 at 06:28
Just out of curiosity how would I go about declaring res_cnt and name as to the original SQL call i did before? – dapi Apr 24 '20 at 06:34
@dapi I do not see the reason to use intermediate variables when we may avoid this. But if you want - you may... in that case you must use BEGIN-END codeblock. – Akina Apr 24 '20 at 07:06

Create MySQL procedures

1 Answers1