如何在SQL存储过程中获取if else中的多个输出参数值？ [英] How to get multiple output parameters values within if else in SQL stored procedure ?

问题描述

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

推荐答案

这个存在在聚合上就像COUNT一样会返回true，因为它总是返回一些值，所以它永远不会访问else部分，

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)

请改为尝试：

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

请更改以下代码： -



if（（从User_Master中选择COUNT（User_Id），其中User_Id = @ userid和密码= @password）> 0）





从User_Master中选择@usertype = User_Type，其中User_Id = @ userid和密码= @password;

设置@loginstatus = 1;

结束

其他

开始

设置@usertype = 0

设置@loginstatus = 0;

结束

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

这篇关于如何在SQL存储过程中获取if else中的多个输出参数值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！