Does this function follow ansi sql standard? 1

[jm89]

Hi,

I was wondering if anyone can tell me if this function follows ANSI SQL standard, and for future reference are there any resources that i can use to find if my SQL conforms to ANSI standards. And the DBMS i am using is MySQL.

CREATE FUNCTION INCREMENT()
RETURNS INT
BEGIN
DECLARE old INT;
DECLARE new INT;
SELECT current INTO old FROM table FOR UPDATE;
SET new=old +1;
UPDATE table SET current=new;
RETURN new;
END;

Thanks.

 

[PHV]

old, new and current are reserved words.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jm89]

Thanks for the reply PHV, i wont actually use those variable names, besides that does it conform to ANSI.

 

[PHV]

Get rid of FOR UPDATE

any resources that i can use to find if my SQL conforms to ANSI standards
Have a look here:

http://developer.mimer.se/validator/parser200x/index.tml

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JarlH]

Does the table always contain exactly one row? If not, a cursor is needed! (Or a where clause that restricts the result set to one row...)

You may also have to specify access mode to allow the table update:

[tt]CREATE FUNCTION INCREMENT()
RETURNS INT
MODIFIES SQL DATA
BEGIN
...[/tt]