help with if insert - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

help with if insert

Author

Topic

skillile
Posting Yak Master

208 Posts

Posted - 2001-11-26 : 12:30:18

Ok my concept (I think is simple) but somewhere my syntax on the if..begin...else..end
is messed up. I want to first check for the type then do the insert pending. I will
put transactions on it later but it always executes the first insert. Any help would
be appreciated.

ALTER PROCEDURE s_mgm_user_add

(
@task int,
@oid int,
@moid int,
@utype int,
@fname varchar(20),
@lname varchar(20),
@email varchar(50),
@login varchar(10),
@pword varchar(10),
@moduser varchar(30),
@modip varchar(30),
@oidr int output
)

as
SET NOCOUNT ON
DECLARE @recid int --user for identity
--check in customer table

if @utype = 1
begin
if exists (SELECT fname from tblusercust
where lower(fname) LIKE lower(@fname) AND lower(lname) LIKE lower(@lname)
)
RETURN 0
else
insert into tbllogin (login, utype, pword, moduser, modip)
values (@login, @utype, @pword, @moduser, @modip)
SET @recid = @@identity

insert into tblusercust (moid, coid, lid, fname, lname, moduser, modip)
values(@moid, @oid, @recid, @fname, @lname, @moduser, @modip)
SET @oidr=@@identity
RETURN 1
end

--check in company table
if @utype = 4
begin
if exists (SELECT fname, lname, email from tblusermet
where lower(fname) LIKE lower(@fname) AND lower(lname) LIKE lower(@lname)
)
RETURN 0

else

insert into tbllogin (login, utype, pword, moduser, modip)
values (@login, @utype, @pword, @moduser, @modip)
SET @recid = @@identity

insert into tblusermet (moid, lid, fname, lname, moduser, modip)
values(@oid, @recid, @fname, @lname, @moduser, @modip)
SET @oidr=@@identity
RETURN 1

end

slow down to move faster...

andre
Constraint Violating Yak Guru

259 Posts

Posted - 2001-11-26 : 12:44:04

Try this:


SET NOCOUNT ON
DECLARE @recid int --user for identity
--check in customer table

IF @utype = 1 BEGIN
  IF exists (SELECT fname from tblusercust where lower(fname) LIKE lower(@fname) AND lower(lname) LIKE lower(@lname) ) BEGIN
    RETURN 0
  END
  ELSE BEGIN
    insert into tbllogin (login, utype, pword, moduser, modip) values (@login, @utype, @pword, @moduser, @modip)
    SET @recid = @@identity

    insert into tblusercust (moid, coid, lid, fname, lname, moduser, modip)
    values(@moid, @oid, @recid, @fname, @lname, @moduser, @modip)
    SET @oidr=@@identity
    RETURN 1
  END
END
--check in company table
IF @utype = 4 BEGIN
  IF exists (SELECT fname, lname, email from tblusermet
               where lower(fname) LIKE lower(@fname) AND lower(lname) LIKE lower(@lname)
               ) BEGIN
    RETURN 0
  END
  ELSE BEGIN
    insert into tbllogin (login, utype, pword, moduser, modip)
               values (@login, @utype, @pword, @moduser, @modip)
    SET @recid = @@identity 
    insert into tblusermet (moid, lid, fname, lname, moduser, modip)
               values(@oid, @recid, @fname, @lname, @moduser, @modip)
    SET @oidr=@@identity
    RETURN 1
  END
END

skillile
Posting Yak Master

208 Posts

Posted - 2001-11-26 : 13:07:37

worked great thanks

slow down to move faster...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources