如何从SQL Server中的存储过程中获得整数形式的输出响应

Question

我正试图从存储过程中获得以下响应，以验证用户身份，并根据用户输入从存储过程读取响应。

CREATE Procedure sp_UserAuthentication
@UserName varchar(20),
@UserPassword varchar(20),
@Resp int output
AS
DECLARE @Count int;
DECLARE @IsAdmin bit;
DECLARE @IsActive bit;
Select @Count = COUNT(UserName) from tbl_UserDetails where UserName = @UserName and UserPassword = @UserPassword
IF @Count = 1
BEGIN

Select @IsActive = IsActive, @IsAdmin = @IsAdmin from tbl_UserDetails where UserName = @UserName and UserPassword = @UserPassword
IF @IsActive = 0 -- InActive user
BEGIN
SET @Resp = 3
END
ELSE IF @IsAdmin = 1 -- Admin user
BEGIN
SET @Resp = 2
END
ELSE -- Normal user
BEGIN
SET @Resp = 1
END
END
ELSE -- InValid user
BEGIN 
SET @Resp = 0
END
GO

我得到的响应对于有效用户总是1，对于无效用户总是0。我没有得到管理员2和非活动用户3的响应。

DECLARE @Res int 
exec sp_UserAuthentication 'user', 'pwd', @Res out
SELECT @Res

表：

Create Table tbl_UserDetails(
UserName Varchar(20) primary key,
UserPassword Varchar(20) NOT NULL,
IsAdmin Bit Default 0,
IsActive Bit Default 1
);

Answer 1

记住每个人对存储明文密码的严厉谴责。您迫切需要阅读并了解如何加密这些内容。但是你的整个过程可以大大简化成这样。

CREATE Procedure UserAuthentication
(
@UserName varchar(20),
@UserPassword varchar(20),
@Resp int output
)
AS
set @Resp = 0 --This sets the response to invalid unless we actually find a user
Select @Resp = 
case when IsActive = 0 then 3 --Inactive User
when IsAdmin = 1 then 2 --Admin user
else 1 --Normal user
end
from tbl_UserDetails 
where UserName = @UserName 
and UserPassword = @UserPassword

Answer 2

让我们忽略您正在将用户名和密码放入表中。请不要这样做，如果这样做，请使用某种加密/哈希。但是看看中间的SELECT语句：

Select @IsAdmin = @IsAdmin

您正在将@IsAdmin设置为@IsAdmin，您需要将其仅设置为IsAdmin。