Building a Stored Procedure

[gregoryday]

I am battling to get a stored procedure to build:

I have a stored procedure working in SQL Server 2000, and now need to port it to DB2. I will be accessing the stored procedure via VB.NET. The stored procedure reads a value from a table, increments the value by 1 and writes it back into the table. The table is called LOADER.CAD_AT_LAST_UNIQUE_ID, and the field is LASTKEY. When I try to build the procedure I get the following message:

SDE.SP_GEN_UNIQUE_ID - Build started.
Create stored procedure returns -20112.
SDE.SP_GEN_UNIQUE_ID: 31: [IBM][CLI Driver][DB2/NT] SQL20112N A SAVEPOINT cannot be set because a SAVEPOINT already exists and nested SAVEPOINTS are not supported. LINE NUMBER=31. SQLSTATE=3B002
SDE.SP_GEN_UNIQUE_ID - Build failed.
SDE.SP_GEN_UNIQUE_ID - Roll back completed successfully.

My code is below. Having consulted the redbook on migrating from SQL Server to DB2, and it's content on SAVEPOINTS, I do not see that I am using these and do not understand why the compiler is reporting the above error.

CREATE PROCEDURE SDE.SP_GEN_UNIQUE_ID (OUT lastKey bigint)
------------------------------------------------------------------------
-- SQL Stored Procedure
/* This procedure retrieves the Last Assigned Key from the Table */
/* The Last Assigned Key is increased by 1 and written back */
------------------------------------------------------------------------
LANGUAGE SQL
-- Transaction has to be atomic if we
-- wish to be able to roll back changes
P1: BEGIN ATOMIC

SELECT LASTKEY INTO lastKEY
FROM LOADER.CAD_AT_LAST_UNIQUE_ID;

SET lastKEY = lastKEY + 1;

IF lastKEY >= 0 THEN
BEGIN
UPDATE LOADER.CAD_AT_LAST_UNIQUE_ID
SET LASTKEY = lastKEY;
END;
END IF;

COMMIT;
END P1

[gregoryday]

Hi All,

Sorted out the problem. I started from scratch, and pieced it all together properly. Working code:

CREATE PROCEDURE SDE.PROCEDURE1 (OUT newUniqueKey BIGINT)
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN
DECLARE lastUniqueKey BIGINT DEFAULT -9999;

SET lastUniqueKey = (SELECT LASTKEY FROM LOADER.CAD_AT_LAST_UNIQUE_ID);
SET newUniqueKey = lastUniqueKey + 1;
UPDATE LOADER.CAD_AT_LAST_UNIQUE_ID SET LASTKEY = newUniqueKey;

END P1

I am battling to get a stored procedure to build:

I have a stored procedure working in SQL Server 2000, and now need to port it to DB2. I will be accessing the stored procedure via VB.NET. The stored procedure reads a value from a table, increments the value by 1 and writes it back into the table. The table is called LOADER.CAD_AT_LAST_UNIQUE_ID, and the field is LASTKEY. When I try to build the procedure I get the following message:

SDE.SP_GEN_UNIQUE_ID - Build started.
Create stored procedure returns -20112.
SDE.SP_GEN_UNIQUE_ID: 31: [IBM][CLI Driver][DB2/NT] SQL20112N A SAVEPOINT cannot be set because a SAVEPOINT already exists and nested SAVEPOINTS are not supported. LINE NUMBER=31. SQLSTATE=3B002
SDE.SP_GEN_UNIQUE_ID - Build failed.
SDE.SP_GEN_UNIQUE_ID - Roll back completed successfully.

My code is below. Having consulted the redbook on migrating from SQL Server to DB2, and it's content on SAVEPOINTS, I do not see that I am using these and do not understand why the compiler is reporting the above error.

CREATE PROCEDURE SDE.SP_GEN_UNIQUE_ID (OUT lastKey bigint)
------------------------------------------------------------------------
-- SQL Stored Procedure
/* This procedure retrieves the Last Assigned Key from the Table */
/* The Last Assigned Key is increased by 1 and written back */
------------------------------------------------------------------------
LANGUAGE SQL
-- Transaction has to be atomic if we
-- wish to be able to roll back changes
P1: BEGIN ATOMIC

SELECT LASTKEY INTO lastKEY
FROM LOADER.CAD_AT_LAST_UNIQUE_ID;

SET lastKEY = lastKEY + 1;

IF lastKEY >= 0 THEN
BEGIN
UPDATE LOADER.CAD_AT_LAST_UNIQUE_ID
SET LASTKEY = lastKEY;
END;
END IF;

COMMIT;
END P1