CODEWITHSUNDEEP
sql-servert-sql
Manju
Manju

Reputation: 2640

How to get a output response as integer from a stored procedure in SQL Server

I'm trying to get the following response from a stored procedure to authenticate the user and read a response from stored procedure based on the user input.

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

I'm getting response as always 1 for a valid user and 0 for invalid user. I'm not getting the response as 2 for admin and 3 for an inactive user.

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

Table:

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

Upvotes: 1

Views: 178

Answers (2)

Sean Lange
Sean Lange

Reputation: 33581

Keep in mind everybody's stern reprimands about storing clear text passwords. You desperately need to read about and understand how to encrypt these. But your entire procedure could be greatly simplified to something like this.

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

Upvotes: 2

paulbarbin
paulbarbin

Reputation: 382

Let's ignore that you're putting usernames and passwords into a table. Please don't do that, and if you do, please use some kind of encryption/hashing. But look in your SELECT statement in the middle:

Select @IsAdmin = @IsAdmin

You are setting @IsAdmin to @IsAdmin, you need to set that to just IsAdmin.

Upvotes: 1

Related Questions