SQL logon Stored Procedure Code

------------------------------------------------------------------------------
-- Login Stored Procedure
------------------------------------------------------------------------------
------ @ Islogin:-1: Password error 0: User name does not exist; 1: Login successful 2: User frozen
Use newsdata
Go
If exists (select * From sysobjects where [name] = 'xp _ login' and [type] = 'P ')
Begin
Drop proc xp_login
End
Go
Create proc xp_login
@ Userid varchar (20 ),
@ Password varchar (20 ),
@ Userstate int output,
@ Islogin int output,
@ Lastlogintime datetime output,
@ Lastloginip varchar (20) output,
@ Usermarker int output
As
Begin
Set @ islogin = (select count (0) from tb_users where userid = @ userid and Password = @ password and userstate = 1 );
If (@ islogin = '1 ')
Begin
Begin
Declare @ thisloginip varchar (20 );
Set @ thisloginip = @ lastloginip;
Set @ lastlogintime = (select lastlogintime from tb_users where userid = @ userid );
Set @ lastloginip = (select lastloginip from tb_users where userid = @ userid );
Set @ usermarker = (select usermarker from tb_users where userid = @ userid );
Update tb_users set lastlogintime = getdate (), lastloginip = @ thisloginip, @ usermarker = @ usermarker + 2 where userid = @ userid;
Select roleid from tb_users_roles where userid = @ userid;
End
End
Else
Begin
Declare @ checkstate int;
Set @ checkstate = (select count (0) from tb_users where userid = @ userid and userstate = 1 );
If (@ checkstate = 1)
Begin
Declare @ checkpwd int;
Set @ checkpwd = 100;
Set @ checkpwd = (select userstate from tb_users where userid = @ userid );
If (@ checkpwd = 1)
Begin
Set @ islogin =-1;
End
Else
Begin
Set @ islogin = 0;
End
End
Else
Begin
Declare @ checkname int;
Set @ checkname = (select count (0) from tb_users where userid = @ userid)
If (@ checkname = 1)
Begin
Set @ islogin = 2;
End
Else
Begin
Set @ islogin = 0;
End
End
End
End
Go
Declare
@ Userstate int,
@ Islogin int,
@ Lastlogintime datetime,
@ Lastloginip varchar (20 ),
@ Usermarker int
Set @ lastloginip = '192. 168.2.116'
Set @ lastlogintime = '2017-1-18'
Exec xp_login 'admin', '000000', @ userstate output, @ islogin output, @ lastlogintime output, @ lastloginip output, @ usermarker output
Print @ islogin
Print @ lastlogintime
Print @ lastloginip