Help to write a stored procedure

If you want to check login credentials against a table in SQL Server, I sugest you do just this:

alter procedure logindetail  
@username varchar(20),  
@password varchar(20)  
 as  
select username, password from login 
where username = @username  
and password = @password  
and @username is not null and @password is not null

If that stored procedure returns a row, your application will know the provided credentials are correct. If no rows are returned, the login credentials are wrong.

If you get no rows returned, you should have your application handle the error messages. I did provide a null-check in the WHERE clause, but your application should make sure no NULL parameters are sent.

Hi, your inner queries in the stored proc should look like this:

if(@username is null or @password is null)  /*It will check the username & password it is not ok return error */ 
begin  
    select 88888,'  Mr.' + upper(SYSTEM_USER) +'    Please Enter the correct username & password'  
    return 0
end
else
begin
    if (exists(select username, password from login  where username = @username and password = @password ))  /*It will check the username & password it is ok login */
    begin
        select 88888,' Mr.' + upper(@username) +' logged in successful '  
        return 2  
    end
    else
    begin
        select 99999,'  Mr.' + upper(SYSTEM_USER) +'    Please Enter the correct username & password'  
        return 1    
    end    
end

I would like to point out a few concerns with what you are wanting to do.

1. You are auditing logins yourself - SQL Server has built-in login auditing, it can be turned on to only log failed logins, or both failed and successful logins.
2. Your check seems to run against a "valid logins" table. The comparison of password and username doesn't seem to be using hashing of the password - I hope this is done before passing the details into the server and not just comparing plaintext against the table. This would be a major security hole in your system. If someone can read the stored passwords, they can spoof the system and do bad things without detection.
3. Your error messages all expect the end user to be male (they all start with 'Mr.'). You may be wanting to only cater for males, but this does reduce your possible user group by approx. 50% of the worlds population!

You can still continue with your implementation by placing the select statement to check if the username and password is valid into the ELSE condition:

ELSE
BEGIN
  IF NOT EXISTS (SELECT * FROM Login WHERE username=@username and password = @password) -- No valid login found
    SELECT 99999,'  Mr.' + upper(SYSTEM_USER) +' Please Enter the correct username & password'  
END

Hi Magnus,Pavel Pawlowski,WilliamD

Thanks for your reply