프로시저 최적화

set ANSI_NULLS OFF
set QUOTED_IDENTIFIER OFF
GO
ALTER Proc [dbo].[up_AccountRejoin]
/*
////////////////////////////////////////////////////////////////////////
//  수정이전 프로시저
////////////////////////////////////////////////////////////////////////
*/
@Userid varchar(15),
@Password varchar(15),
@Identitynum char(20),
@reVal int output
as
Set Nocount on
Set Xact_Abort On
Declare @Globalid int

DECLARE @Name nvarchar(10)
DECLARE @Identity char(50)
DECLARE @Identitynumflag char(50)
DECLARE @Location1 nvarchar(50)
DECLARE @Location2 nvarchar(50)
DECLARE @address nvarchar(50)
DECLARE @Telnum varchar(14)
DECLARE @Celnum varchar(14)
DECLARE @Email varchar(50)
DECLARE @Emailw char(1)
DECLARE @Level char(1)
DECLARE @Smsflag char(1)
DECLARE @Emailflag char(1)
DECLARE @LeftDate datetime
DECLARE @Pclass char(1)
DECLARE @Ticket int
DECLARE @Sex char(1)
DECLARE @Age char(10)

--SELECT Process
IF EXISTS(Select 1 from tbl_Login a inner join tbl_LeaveMember b on a.f_globalid = b.f_globalid
  where a.f_loginid = @Userid AND a.f_password = @Password AND b.f_identitynum = @Identitynum )
Begin

 SELECT @Globalid = f_globalid FROM tbl_Login  WHERE f_loginid = @Userid  AND f_password = @Password
 
 SELECT @Name = f_name, @Identity = f_identitynum, @Identitynumflag = f_Identitynumflag,  @Location1 = f_location1, @Location2 = f_location2, @address = f_address, @Telnum = f_telnum, @Celnum = f_celnum, @Email = f_email, @Emailw = f_emailw, @LeftDate = f_leftdate, @Level = f_level, @Smsflag = f_smsflag, @Emailflag = f_emailflag, @Pclass = f_pclass, @Ticket = f_ticket, @Sex = f_sex, @Age = f_age
 FROM tbl_LeaveMember WHERE f_globalid = @Globalid
 
 UPDATE tbl_Member SET f_name = @Name, f_identitynum = @Identity, f_Identitynumflag = @Identitynumflag, f_location1 = @Location1, f_location2 = @Location2, f_address = @address, f_telnum = @Telnum, f_celnum = @Celnum, f_email = @Email, f_emailw = @Emailw, f_level = @Level, f_smsflag = @Smsflag, f_emailflag = @Emailflag, f_leftdate = NULL, f_pclass = @Pclass, f_ticket = @Ticket, f_sex = @Sex, f_age = @Age
 WHERE f_globalid = @Globalid

 set @reVal = 0 -- 정상 종료
 return
End
ELSE
Begin
 set @reVal = 2  -- 정보 틀림
 return
End

set ANSI_NULLS OFF
set QUOTED_IDENTIFIER OFF
GO
ALTER Proc [dbo].[up_AccountRejoin]
/*
////////////////////////////////////////////////////////////////////////
// 수정후
////////////////////////////////////////////////////////////////////////
*/
@Userid varchar(15),
@Password varchar(15),
@Identitynum char(20),
@reVal int output
as
Set Nocount on
Set Xact_Abort On

--SELECT Process
IF EXISTS(Select 1 from tbl_Login a inner join tbl_LeaveMember b on a.f_globalid = b.f_globalid
  where a.f_loginid = @Userid AND a.f_password = @Password AND b.f_identitynum = @Identitynum )
Begin
 Begin Tran
 Update tbl_Member SET f_name = B.f_name, f_identitynum = B.f_identitynum, f_Identitynumflag = B.f_Identitynumflag, f_location1 = B.f_location1, f_location2 = B.f_location2,
 f_address = B.f_address, f_telnum = B.f_telnum, f_celnum = B.f_celnum, f_email = B.f_email, f_emailw = B.f_emailw, f_level = B.f_level, f_smsflag = B.f_smsflag,
 f_emailflag = B.f_emailflag, f_leftdate = NULL, f_pclass = B.f_pclass, f_ticket = B.f_ticket, f_sex = B.f_sex, f_age = B.f_age 
 From tbl_Member U
 inner Join tbl_Login A on U.f_globalid = A.f_globalid
 Inner Join tbl_LeaveMember B on A.f_globalid = B.f_globalid
 where A.f_loginid =@Userid
 IF @@ERROR <> 0 GOTO errorHandler

 UPDATE  tbl_Login SET f_status = 1  WHERE f_loginid =@Userid
 IF @@ERROR <> 0 GOTO errorHandler

 DELETE From tbl_LeaveMember From tbl_LeaveMember A inner Join  tbl_Login B on A.f_globalid = B.f_globalid WHERE B.f_loginid =@Userid
 IF @@ERROR <> 0 GOTO errorHandler
 
 Commit Tran
 set @reVal = 0 -- 정상 종료
 return
End
ELSE
Begin
 set @reVal = 2  -- 정보 틀림
 return
End

errorHandler:
  ROLLBACK TRAN
  SET @reVal = 1
  RETURN