userdefined exceptions in mysql

[vijaya Lakshmi]

Hi ,

We are trying to convert stored procedures from mssql to mysql as a part of db migration, and we are facing the following issue.

In mssql , business exceptions can be raised by using RAISERROR function as given below

CREATE PROCEDURE dbo.NEXT_SEQ_VAL(@seqName VARCHAR(100),@increment int = 1)
AS
BEGIN
  DECLARE @NSN int,
  @TRIES int
 
  IF @increment < 1
  BEGIN
    RAISERROR('increment parameter must be > 0', 16, 1)
    RETURN -1
  END
SELECT @NSN = SEQUENCE_INDEX FROM SEQUENCE_GEN WHERE SEQUENCE_NAME = @seqName
  --PRINT 'NSN=' + ISNULL(convert(varchar,@NSN),'NULL')
  IF @NSN IS NULL
BEGIN
    --PRINT 'INSERT SEQUENCE_GEN ROW FOR ' + @seqName
    INSERT INTO SEQUENCE_GEN(SEQUENCE_NAME,SEQUENCE_INDEX) VALUES (@seqName,1)
    SET @NSN = 1
  END
RETURN @NSN
END
 

for migrating the above code to mysql i have three doubts
1.how can i set the default value for increment parameter to 1.
2.how can i raise a business exception like above.
3.as i know procedures does not return any value but function will return values,then
in the above code return -1 is doing what?and how can i do the same in mysql.
please do the need full as soon as possible.

Thanks in advance,

Vijaya.

[Atli]

Hi.
To answer your questions:

1. You can't. MySQL (unfortunetly) does not support default values for procedure parameters. All parameters declared must be passed.
   
2. MySQL does not yet provide a method of calling a custom error, such as the MSSQL RaiseError function. In the 5.x versions, you must "provoke" and error by using syntax that would cause an error. Like say, calling a procedure that doesn't exist, which would cause an error that your client could catch.
3. That's correct, procedures do not return values, but functions do. The syntax for both is very similar tho so it shouldn't be a problem to convert your procedure into a function. Check out the manual to see the differences.