I have a table contain field name as user_id and user_password. I want to check the password is matching or not using the stored procedure.

1. Please check security Concern when you are using plain text to save password.

2. You have to change your Procedure like:

create procedure testlogin
@usrid varchar(50),@usrpass varchar(50)
as
declare @variable int=0
select @variable=Count(User_ID) from test_login where User_id=@usrid and User_password=@usrpass collate SQL_Latin1_General_CP1_CS_AS
if @variable>0
begin
select 1
end
else if @variable=0
begin
select 0
end
else
begin
select -1
end


this procedure will return values Like Below:

1. If user found then it will return "1"
2. If user Not found then it will return "0"
3. else -1 (I don't know why you are using this status that's why I am adding this in else part)


you can get Procedure in
your Database/Programmability/Store Procedure
or you can use
sp_helptext "your Procedure name"

Ashish

My answer would basically be same as OriginalGriff.

* When it comes to passwords, we should never store them in plain text.
* Nor they should be stored encrypted.
* They should always be stored as a hash value.
* Also, the password hashing process(at the time of registration) and checking the hash(at the time of login) should not be in the database procedures. It should always be in the application.
* If there are multiple applications that want to use the same db for authentication, either have the registration and login functionality pushed inside a class library or a Service.

For more details on why to store hashes and how to store and compare them, please see the below article.

A Beginner's Tutorial for Understanding and Implementing Password Hashing and Salting[^]