SQL 在存储过程中验证用户的最简单方法？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8895039/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Easiest way to validate user in stored procedure?
提问by The Vanilla Thrilla
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 loginand passwordto see if they match in the database. Is there a simple way to do this?
我需要一个存储过程，它可以在登录尝试时通过发送login和password查看它们是否在数据库中匹配来检查它们是否是有效用户。有没有一种简单的方法可以做到这一点？
回答by Mack
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.
根据您的响应修改查询 - 这将返回字符串“true”或“false”，如果您愿意，您可以分别用位值 1 和 0 替换它们。
回答by Arion
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
回答by Shahbaz Raees2
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
回答by arielreyesflores
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
回答by Ashokkumar M K
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 .
这里我把员工作为表，用户名和密码有员工表。
