Return value based on count from SQL Server Stored Procedure

Question

I have the following SQL Server Stored Procedure which validates a password.

ALTER PROC [dbo].[spValidatePassword]
@UserId uniqueidentifier,
@Password NVARCHAR(255)
AS
BEGIN

DECLARE @PasswordHash NVARCHAR(255) = HASHBYTES('SHA2_512', (SELECT @Password + CAST((SELECT p.PasswordSalt FROM Passwords p WHERE p.UserId =     @UserId) AS NVARCHAR(255))))

SELECT COUNT(*)
from Passwords
WHERE UserId = @UserId
AND [Password] = @PasswordHash


--return 1 if valid password
--return 0 if not valid

END

How can I return 1 from the stored procedure if the count is greater than zero, and zero otherwise?

Abdul Rasheed · Accepted Answer · 2016-05-10 09:11:06Z

3

Try this query, will return 1 if there is a result, else 0

SELECT (CASE WHEN COUNT(*) > 1 THEN 1 ELSE 0 END)
FROM Passwords
WHERE UserId = @UserId
AND [Password] = @PasswordHash

answered May 10, 2016 at 9:11

Abdul Rasheed

6,7794 gold badges36 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Nick.Mc Over a year ago

and if there are 10 matching rows this select returns 10 rows of 1's

Abdul Rasheed Over a year ago

@Nick.McDermaid, No, Never, u can try COUNT in a simple table with a WHERE clause.

Nick.Mc Over a year ago

Oh! Interesting I never knew that. I take it back.

Nick.Mc · Accepted Answer · 2016-05-10 09:09:36Z

2

ALTER PROC [dbo].[spValidatePassword]
@UserId uniqueidentifier,
@Password NVARCHAR(255)
AS
BEGIN

DECLARE @PasswordHash NVARCHAR(255) = HASHBYTES('SHA2_512', (SELECT @Password + CAST((SELECT p.PasswordSalt FROM Passwords p WHERE p.UserId =     @UserId) AS NVARCHAR(255))))

SELECT 
CASE WHEN EXISTS (
         SELECT *
         from Passwords
         WHERE UserId = @UserId
         AND [Password] = @PasswordHash
         )
 THEN 1 
 ELSE 0
 END


--return 1 if valid password
--return 0 if not valid

END

But consider using some other authentication model like OAuth or Office 365 logins rather than reinventing the wheel

answered May 10, 2016 at 9:09

Nick.Mc

19.4k6 gold badges67 silver badges102 bronze badges

1 Comment

Graham Over a year ago

Thanks Nick. The authentication is using OWIN but it is against a legacy database.

Akshey Bhat · Accepted Answer · 2016-05-10 09:10:05Z

0

IF    (SELECT COUNT(UserId) from Passwords WHERE UserId = @UserId  AND [Password] = @PasswordHash) > 0
begin
    return 1;
End
ELse
begin
   return 0;
end

this query will return the required result

edited May 10, 2016 at 9:10

answered May 10, 2016 at 9:06

Akshey Bhat

8,6872 gold badges24 silver badges20 bronze badges

4 Comments

Nick.Mc Over a year ago

No. If there are 3 matching records this will return 3. The result is no different to COUNT(*)

Akshey Bhat Over a year ago

check modified answer

Nick.Mc Over a year ago

OK so now this one returns data using RETURN rather than SELECT

Akshey Bhat Over a year ago

you can use select 1 in place of return 1

Chaos Legion · Accepted Answer · 2016-05-10 09:13:42Z

0

IF EXISTS (
         SELECT *
         from Passwords
         WHERE UserId = @UserId
         AND [Password] = @PasswordHash
         )
RETURN 1;
ELSE
RETURN 0;

answered May 10, 2016 at 9:13

Chaos Legion

3,0101 gold badge17 silver badges14 bronze badges

Comments

varun kumar dutta · Accepted Answer · 2016-05-10 12:35:25Z

0

You can also use OUTPUT Type stored procedure to get a scalar (in your case either 1 or 0) result as:

ALTER PROC [dbo].[spValidatePassword]
@UserId uniqueidentifier,
@Password NVARCHAR(255),
@result TINYINT OUTPUT
AS
BEGIN

DECLARE @PasswordHash NVARCHAR(255) = HASHBYTES('SHA2_512', (SELECT @Password + CAST((SELECT p.PasswordSalt FROM Passwords p WHERE p.UserId =     @UserId) AS NVARCHAR(255))))

SELECT @result = COUNT(*)
from Passwords
WHERE UserId = @UserId
AND [Password] = @PasswordHash

END

You can execute this proc as:

DECLARE @output TINYINT

EXEC [dbo].[spValidatePassword]  @UserId= 'any user id',@Password = 'any password',@result = @output OUTPUT

PRINT @output

answered May 10, 2016 at 12:35

varun kumar dutta

2021 gold badge5 silver badges10 bronze badges

Collectives™ on Stack Overflow

Return value based on count from SQL Server Stored Procedure

5 Answers 5

3 Comments

1 Comment

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

1 Comment

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related