Re: Can insert but not update in stored procedure ?

Hi Gerry,

        Your procedure is good. However, the update, though working, doesn't SEEM to perform the update. Since you've named your input and output variables the same (except for userid), the update goes through and updates each table variable to the same table variable given the userid - instead of the input variable. If you name the input variables within the procedure differently, or the table variables and make the appropriate changes within this procedure - you'll find it works very well.

Greg

Gerry West wrote:
>
> Hi All,
>
> I've attempted my first stored procedure with partial success.
> I can insert but not update, do I need some kind of special privilege
> granted ?
> Also what is the difference between IS and AS in the procedure creation ?
>
> I executed it in SQL*PLUS using the following lines:
>
> One to insert:
> EXECUTE sp_insert_user (99, 'West', 'Gerry', 15, '303444-5555', '8888' )'
> Commit;
> Another to update the existing user:
> EXECUTE sp_insert_user (99, 'Gerry', 'West', 11, '1111111111', '1111' )'
> Commit
>
> Compiled Procedure:
>
> CREATE OR REPLACE PROCEDURE sp_insert_user( userid IN NUMBER, user_name IN
> VARCHAR, password IN VARCHAR,
> security_level IN NUMBER,
> paging_phone_number IN VARCHAR,
> pin_number IN VARCHAR
> )AS
> BEGIN
> -- Update an existing user
> UPDATE usrs_users
> SET user_name = user_name,
> password = password,
> security_level = security_level,
> paging_phone_number = paging_phone_number,
> pin_number = pin_number
> WHERE user_id = userid;
>
> -- Else insert a new user
> IF SQL%NOTFOUND THEN
> INSERT INTO usrs_users
> VALUES( userid,
> user_name,
> password,
> security_level,
> paging_phone_number,
> pin_number);
> END IF;
> END sp_insert_user;
> /
>
> Thanks in advance,
> Gerry
  Received on Mon Feb 02 1998 - 00:00:00 CST