Easiest way to validate user in stored procedure?

Question

I need a stored procedure that can check to see if, on a login attempt, whether or not they are a valid user by sending the login and password to see if they match in the database. Is there a simple way to do this?

Solution 1

Without more information the best I can offer for the moment is:

CREATE STORED PROCEDURE CheckPassword
    @username VARCHAR(20),
    @password varchar(20)
AS
BEGIN

SET NOCOUNT ON

IF EXISTS(SELECT * FROM usertable WHERE username = @username AND password = @password)
    SELECT 'true' AS UserExists
ELSE
    SELECT 'false' AS UserExists

END

Query amended based on your response - this will return the string 'true' or 'false' you could replace them with bit values 1 and 0 respectively if you prefer.

Solution 2

This might help:

CREATE PROCEDURE CheckPassword
    @username VARCHAR(20),
    @password varchar(20)
AS
BEGIN

SET NOCOUNT ON

SELECT CASE WHEN EXISTS(SELECT NULL FROM usertable WHERE userName=@username AND password=@password)
    THEN CAST(1 AS BIT)
    ELSE CAST(0 AS BIT)
END

END