Create stored proc mysql 5.0

[E.N.]

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

[Bill Karwin]

Re: Create stored proc mysql 5.0

E.N. wrote:[color=blue]
> 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[/color]

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.

[E.N.]

Re: Create stored proc mysql 5.0

"Bill Karwin" <bill@karwin.co m> wrote in message
news:cks974019s 0@enews2.newsgu y.com...[color=blue]
> E.N. wrote:[color=green]
>> 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[/color]
>
> I think the @ syntax may be a Microsoft-specific syntax.[/color]

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?
[color=blue]
> MySQL follows the SQL:2003 syntax for stored procedures, which is also
> used by IBM's DB2.[/color]

According to the docs ansi sql 99 ... but not an important detail here.
[color=blue]
> 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[/color]

ah I've tried without the @ sign but the IN was missing.
Thanks!
[color=blue]
> Regards,
> Bill K.[/color]