By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,341 Members | 1,388 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,341 IT Pros & Developers. It's quick & easy.

Create stored proc mysql 5.0

P: n/a
Hi
For testing & developing I try to create a stored proc using the mysql query
tool.

But it complains about syntax. I've tried to see what help and other samples
could return but they do not write about how it really must be done..

this is syntactically a right proc body

create procedure doit
(@p int)
AS
BEGIN

SELECT * FROM TEST2 WHERE id = @p ;

END

Jul 20 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
E.N. wrote:
Hi
For testing & developing I try to create a stored proc using the mysql
query tool.

But it complains about syntax. I've tried to see what help and other
samples could return but they do not write about how it really must be
done..

this is syntactically a right proc body

create procedure doit
(@p int)
AS
BEGIN

SELECT * FROM TEST2 WHERE id = @p ;

END


I think the @ syntax may be a Microsoft-specific syntax.
MySQL follows the SQL:2003 syntax for stored procedures, which is also
used by IBM's DB2.
Refer to http://dev.mysql.com/doc/mysql/en/CREATE_PROCEDURE.html

I think for your example, the syntax would be:

CREATE PROCEDURE doit (IN p INT)
BEGIN
SELECT * FROM TEST2 WHERE id = p;
END

Regards,
Bill K.
Jul 20 '05 #2

P: n/a
"Bill Karwin" <bi**@karwin.com> wrote in message
news:ck*********@enews2.newsguy.com...
E.N. wrote:
Hi
For testing & developing I try to create a stored proc using the mysql
query tool.

But it complains about syntax. I've tried to see what help and other
samples could return but they do not write about how it really must be
done..

this is syntactically a right proc body

create procedure doit
(@p int)
AS
BEGIN

SELECT * FROM TEST2 WHERE id = @p ;

END
I think the @ syntax may be a Microsoft-specific syntax.


Hi,

I've tried it both but it ends at line 2.
Maybe the syntax is OK, but where (in which tool) do I create new procs?
MySQL follows the SQL:2003 syntax for stored procedures, which is also
used by IBM's DB2.
According to the docs ansi sql 99 ... but not an important detail here.
Refer to http://dev.mysql.com/doc/mysql/en/CREATE_PROCEDURE.html

I think for your example, the syntax would be:

CREATE PROCEDURE doit (IN p INT)
BEGIN
SELECT * FROM TEST2 WHERE id = p;
END
ah I've tried without the @ sign but the IN was missing.
Thanks!
Regards,
Bill K.


Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.