Easiest way to validate user in stored procedure?

问题:

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?

回答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.

回答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

回答3:

Create proc usp_ValidateStoreKeeperLogin 
@SalesmanCode VARCHAR(50)
,@LogisticUserCode VARCHAR(50)
,@LogisticUserPassword VARCHAR(50)

AS 
BEGIN

if EXISTS(select 1 from tblUser where Code=@LogisticUserCode And [password]=@LogisticUserPassword  )
SELECT '1234' SalesmanCode,'12345' LogisticUserCode,'12346' DistributorCode,1 as ReturnValue,'Success' AS Msg from tblUser

select 'INVALID USER CODE' AS Msg ,-1 as ReturnValue


END

回答4:

go
CREATE PROC usp_ValidateUser
( @userName VARCHAR(50),
    @password VARCHAR(50)
)
AS
begin
declare @credentials TABLE(
    username varchar(50),
    userPassword varchar(50)
)

SELECT null,
    CASE WHEN NombreUsuario = 'korn' and PasswordUsuario = HASHBYTES('sha1', '1234') THEN  cast(1 as bit)
        ELSE   cast(0 as bit) end as TieneAcceso

FROM Usuarios;
end

回答5:

Create procedure validate_data

@username varchar(20),
@password varchar(20)

As 
Begin 
  If exists (select * from employee where username=@username and password=@password)
  Raiserror('Exists'16,1)
Else 
  Raiserror('Not Exists'16,1)
End

Here I take employee as table and username and password has the employee tables .