Re: Stored procedure help
Bandaru Prasad (sbandaru@lynx02.dac.neu.edu) wrote:
: CREATE FUNCTION GetNextSeqNumber (tablename char(20))
:
: RETURNING INTEGER;
:
: -- Define the variables Now
: DEFINE Keyvalue INTEGER;
: DEFINE RowsUpdated INTEGER;
:
: -- NOW EXECUTE THE SQL STATEMENT
: UPDATE SequenceNumber
: SET KeyValue = CurrentSeqNumber = NextSeqNumber,
: NextSeqNumber = (NextSeqNumber + IncrementBy),
: --LastUpdateDate = today
: WHERE TableName = tablename
: AND MaximumValue > (NextSeqNumber + IncrementBy)
:
: --Now get the number of rows updated into the local varible.
: LET RowsUpdated = DBINFO('sqlca.sqlerrd2');
:
: --Now Check if Succesfull
: IF RowsUpdated = 0
: RETURN -100
: ELSE
: RETURN KeyValue
: END IF
:
: END FUNCTION
1. Which version of the DBMS is this? If 9.X, then the FUNCTION
syntax is fine. Why? Blame it on the standards committee.
2. Try this: (see below)
CREATE FUNCTION GetNextSeqNumber ( arg_tablename char(20),
IncrementBy INTEGER )
RETURNING INTEGER;
-- Define the variables Now
DEFINE Keyvalue INTEGER;
DEFINE RowsUpdated INTEGER;
-- NOW EXECUTE THE SQL STATEMENT
LET Keyvalue = ( SELECT CurrentSeqNumber
FROM SequenceNumber
WHERE TableName = arg_tablename );
UPDATE SequenceNumber
SET CurrentSeqNumber = CurrentSeqNumber + IncrementBy,
LastUpdateDate = today
WHERE TableName = tablename;
--Now get the number of rows updated into the local varible.
LET RowsUpdated = DBINFO('sqlca.sqlerrd2');
-Now Check if Succesfull
IF RowsUpdated != 1 THEN
RAISE EXCEPTION -946, "Oh No! Wrong Table name?";
END IF;
RETURN KeyValue
END FUNCTION
Notes:
A. The SELECT and the UPDATE need to be separated. EAch kind of
SQL query -- SELECT, INSERT, UPDATE and DELETE -- has a
singular purpose. This is very important, for reasons
to do with transaction isolation. For example, you
can't use this function in a SELECT query.
B. The IncrementBy variable. There is no notion of
a global variable name-space in SQL. You might consider
creating another function GetIncrementVar() that only
ever returns the value, or -- as here -- pas it into
the FUNCTION as an argument.
C. SPL is picky with things like IF ( expre ) THEN ( expr ) END IF;
D. I would not return -100 here. I would raise an exception.
Then make sure that your client code catches it
appropriately.
3. Read up in the manuals about the SERIAL type, which makes all
of this unnecessary.
KR
Pb