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
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
