I want to create a simple insert stored procedure in db2, does anyone has a sample code? Thanks in advance for help!

In Microsoft SQL Server we can do like this:

CREATE PROCEDURE [dbo].[Insert_authors]
@au_id varchar(11),
@au_lname varchar(40),
@au_fname varchar(20),
AS
SET NOCOUNT ON

INSERT INTO [authors]
([au_id],[au_lname],[au_fname])
VALUES
(@au_id,@au_lname,@au_fname)

Can we create simliar stored procedure in DB2 as below:

CREATE PROCEDURE Insert_PROJECT
(
IN p_PROJNO CHAR(6),IN p_PROJNAME VARCHAR(24)
)

DYNAMIC RESULT SETS 0
MODIFIES SQL DATA
LANGUAGE SQL

BEGIN

INSERT INTO PROJECT
(PROJNO,PROJNAME)
VALUES
(p_PROJNO,p_PROJNAME)

END

I am not sure if you can use import parameter directly in db2, if not, we must declare local varible, right? Is there other solutions, such as use table and procedure prefix.

Your DB2 SP looks OK (syntactically) to me.

I do not understand you final question. Could you clarify it?

Andy

I am new to db2 stored procedure, I found in several sample code, it did not use input parameter directly, instead they declare and set local varible like below:

DECLARE PROJNO_VAR CHAR(6);
SET PROJNO_VAR = p_PROJNO;

Then use local varible in SQL insert statement:

insert into PROJECT (PROJNO) VALUES (PROJNO_VAR);

So my question is: do we must declare and set local varible?

Thanks for help...

Kevin

No, the way you wrote it in your first post should work.

Andy

Thank you, Andy.