DB2 Stored procedure syntax

Simple Example

In this article i want to show db2 stored procedure syntax .

Now we have Table "USERS" where ID is identity and CREATE_DATE column is default with option current timestamp.

CREATE TABLE "MYSCHEMA"."USERS" (

"ID" INTEGER NOT NULL GENERATED ALWAYS

AS IDENTITY (START WITH 1, INCREMENT BY 1, CACHE 20,

NO MINVALUE, NO MAXVALUE, NO CYCLE, NO ORDER),

"FIRSTNAME" VARCHAR(255),

"LASTNAME" VARCHAR(255),

"CREATE_DATE" TIMESTAM DEFAULT

)

IN "USERSPACE1";

Stored procedure syntax which will insert data in user table

looks like this . Pay attention that we don't have symbol '@' in procedure parameter

as it is in MSSQL .

CREATE PROCEDURE SP_INSERT_USER

(

IN FIRSTNAME_P VARCHAR(255),

IN LASTNAME_P VARCHAR(255)

)

BEGIN

INSERT INTO USERS (FIRSTNAME ,LASTNAME)

VALUES (FIRSTNAME_P , LASTNAME_P);

END;

To execute procedure from editor

CALL SP_INSERT_USER('User Name','User Surname',para);