RE:Newbie here, have a comment or two and need help.

Tom Brennan wrote :

>d) This stored procedure is really bugging me and would appreciate any help. 
>Basically, I have created a login table to hold various columns, and what I 
>want to do is to check if a login id exists or not, if it doesn't then do an 
>insert.This sounds trivial, but I cannot get it to work! :-( Any comments, 
>correct way of doing this etc are appreciated. I have read the posting on 
>stored procedures/transactions dated Monday 11th March 2002 by a 
>[email protected] and have based the transaction code fom that posting.

>create dbproc sp_add_login_dets(in login_id varchar(15), in login_passwd 
>varchar(15),  in hq_ma_distributor_code varchar(15), in security_level 
>integer, in login_revoked boolean) as
>begin
>    subtrans begin
>        --Check the table for existing user!
>        select
>            user_id,
>            sec_lvl_id,
>            hq_ma_dstrbtr_code,
>            user_pwd,
>            revoked
>        from login_tbl
>        where
>            user_id = :login_id and
>            hq_ma_dstrbtr_code = :hq_ma_distributor_code;
>    --rc of 100 means no row found
>    if $rc = 100
>    then
>        begin
>            --insert it into the table
>            insert into login_tbl (:login_id, :security_level, 
>:hq_ma_distributor_code, :login_passwd, :login_revoked);
>            --rc of 0 means success
>            if $rc = 0 then subtrans end
>            else subtrans rollback
>        end
>end
>/*
>Get the following messages under SQL Studio 7.4.3.6...
>Native Error: -5016
>Text: [SAP AG][SQLOD32 DLL][SAP DB]General Error;-5016 POS(208) Missing 
>delimiter: ;.

Please see the following corrected version. I just inserted some
semicolons at the end of some statements.

create dbproc sp_add_login_dets(in login_id varchar(15), in login_passwd 
varchar(15),  in hq_ma_distributor_code varchar(15), in security_level 
integer, in login_revoked boolean) as
begin
    subtrans begin;
        -- Check the table for existing user! 
        select
            user_id,
            sec_lvl_id,
            hq_ma_dstrbtr_code,
            user_pwd,
            revoked
        from login_tbl_owner.login_tbl
        where
            user_id = :login_id and
            hq_ma_dstrbtr_code = :hq_ma_distributor_code;
    -- rc of 100 means no row found 
    if $rc = 100
    then
        begin
            -- insert it into the table 
            insert into login_tbl_owner.login_tbl (:login_id, :security_level, 
:hq_ma_distributor_code, :login_passwd, :login_revoked);
            -- rc of 0 means success 
            if $rc = 0 then subtrans end
            else subtrans rollback;
        end;
end;

Please note, that in addition you have to specify the owner of the table login_tbl
inside your db-procedure (I named him login_tbl_owner in the corrected version). 

Regards,
Thomas

-- 
Thomas Anhaus
SAP DB, SAP Labs Berlin
[email protected]
http://www.sapdb.org/
_______________________________________________
sapdb.general mailing list
[email protected]
http://listserv.sap.com/mailman/listinfo/sapdb.general