stored procedure

Question

i have written a sp for cheching the existance of a user,is this code right / wrong but i got wrong results
my sp:

ALTER PROCEDURE dbo.sp_UserDetailsInsertUpdate
    (
    @iUid int,
    @vUserName varchar(50),
    @vUserId varchar(50),
    @vPassword varchar(50),
    @iUserLevel int,
    @blockType int
    )

AS
    SET NOCOUNT ON
    If(@blockType=0)
    Begin
    IF EXISTS(SELECT vUserId,iUserLevel FROM tblUserDetails WHERE vUserName=@vUserName AND iUserLevel=@iUserLevel)
    RETURN
    ELSE
    INSERT INTO tblUserDetails(vUserName,vUserId,vPassword,iUserLevel)VALUES(@vUserName,@vUserId,@vPassword,@iUserLevel)
    select @@identity as RowID
    End
    ELSE IF(@blockType=1)
    Begin
    UPDATE tblUserDetails SET vUserName=@vUserName,
    vUserId=@vUserId,vPassword=@vPassword,
    iUserLevel=@iUserLevel WHERE iUid=@iUid
    SELECT @iUid As RowID
    End
    RETURN

Akkiraju Ivaturi · Answer

There is a small issue with begin and end blocks. If if...else statement has only one line then begin..end is not required otherwise we need to keep begin..end like how we do it in C#. Not like how we do it in VB.NET. I have also changed @@identity to Scope_Identity(). Because in real time situations @@Identity will lead to problems. If you have any questions please let me know. I will try to help you out. alter PROCEDURE dbo.sp_UserDetailsInsertUpdate ( @iUid int @vUserName varchar(50) @vUserId varchar(50) @vPassword varchar(50) @iUserLevel int @blockType int ) AS SET NOCOUNT ON If(@blockType=0) Begin IF EXISTS(SELECT UserIdUserLevel FROM UserDetails WHERE UserName=@vUserName AND UserLevel=@iUserLevel) RETURN ELSE Begin INSERT INTO UserDetails(UserNameUserIdPasswordUserLevel)VALUES(@vUserName@vUserId@vPassword@iUserLevel) --select @@identity as RowID --@@identity always gives latest id inserted in the table. So if two users are inserting records --at one time it may give wrong id to you. --so always use scope_identity function to get the id of the record that your session is inserting select SCOPE_IDENTITY() as RowID End End ELSE IF (@blockType = 1) Begin UPDATE UserDetails SET UserName=@vUserName UserId=@vUserIdPassword=@vPassword UserLevel=@iUserLevel WHERE Uid=@iUid SELECT @iUid As RowID End RETURN