Graham Graham - 6 months ago 18
SQL Question

Return value based on count from SQL Server Stored Procedure

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?

Answer
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

Comments