Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2004
    Posts
    34

    Unanswered: DB2 insert stored procedure

    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.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Your DB2 SP looks OK (syntactically) to me.

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

    Andy

  3. #3
    Join Date
    Jun 2004
    Posts
    34
    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

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    No, the way you wrote it in your first post should work.

    Andy

  5. #5
    Join Date
    Jun 2004
    Posts
    34
    Thank you, Andy.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •