Click here to Skip to main content
15,888,803 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am writing stored procedure for login in which i want result through output parameters usertype and loginstatus values as 1 and 1 respectively.

Following is stored procedure code:

CREATE procedure [dbo].[usp_login]
(@userid nvarchar(50), @password nvarchar(50), @usertype int output, @loginstatus int output)
As

Begin

if exists(Select COUNT(User_Id) from User_Master where User_Id = @userid and Password = @password)

begin
select @usertype = User_Type from User_Master where User_Id = @userid and Password = @password;
set @loginstatus = 1;
end
else
begin
set @usertype = 0
set @loginstatus = 0;
end
End

I am executing the sp in ssms as follows:

declare @usrtype int;
declare @lgnstat int;

exec usp_login 'a', 'a', @usrtype output, @lgnstat output

print @usrtype
print @lgnstat

Now when i pass correct userid password i get result as 1,1 which is as expected.
But, when i pass wrong userid or password i get result as 1 which is expected as 0,0.

Please tell me where's the problem either in sp definition code or execution code ?


What I have tried:

CREATE procedure [dbo].[usp_login]
(@userid nvarchar(50), @password nvarchar(50), @usertype int output, @loginstatus int output)
As

Begin

if exists(Select COUNT(User_Id) from User_Master where User_Id = @userid and Password = @password)

begin
select @usertype = User_Type from User_Master where User_Id = @userid and Password = @password;
set @loginstatus = 1;
end
else
begin
set @usertype = 0
set @loginstatus = 0;
end
End


declare @usrtype int;
declare @lgnstat int;

exec usp_login 'z', 'z', @usrtype output, @lgnstat output -- passing wrong userid password 

print @usrtype
print @lgnstat
Posted
Updated 14-Feb-17 2:03am
v2

This "exists" on aggregate like COUNT will always returns true since it always returns some value, so it will never get to visit the "else" part,
if exists(Select COUNT(User_Id) from User_Master where User_Id = @userid and Password = @password)
Try this instead:
if exists(Select 1 from User_Master where User_Id = @userid and Password = @password)
 
Share this answer
 
v2
Comments
Karthik_Mahalingam 5-Feb-17 3:55am    
5
Please change the code like this:-

if ((Select COUNT(User_Id) from User_Master where User_Id = @userid and Password = @password)>0)


select @usertype = User_Type from User_Master where User_Id = @userid and Password = @password;
set @loginstatus = 1;
end
else
begin
set @usertype = 0
set @loginstatus = 0;
end
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900