Sql query returning a table or table?

Question

In my web application, I am writing a request to verify the user ID and password. If userid does not work, I write the request that it displays is incorrect if the password is incorrect and the password is incorrect. The query I'm writing just returns an int, but I want to return a table or row of data, can you give me a solution?

This is my request:

user table consists of username, email, mobile phone, country, etc.,

create procedure [dbo].[sp_users_login] (
  @username varchar(30),
  @password varchar(30),
  @ret int output)
as
 if exists (select username from users where username=@username)
   if exists (select [password], username 
                from users 
                where [password]=@password 
                  and username=@username)
     set @ret =1
   else 
     set @ret=2 
 else 
   set @ret=3

My request will only return int, but I also need user data. For example: user, emailid, etc. I want to get detailed information about a specific user - is this possible in this request?

Answer 1

HAVING , .

SELECT, . CASE .

SELECT 
  CASE 
    WHEN COUNT(*) = 0 THEN 'No username' 
    WHEN MAX(password) = @password THEN 'Logged in' 
    ELSE 'Bad password'
  END as LoginStatus,
  MAX(emailaddress) as Email
FROM dbo.Users u
WHERE username = @username
;

MAX(emailaddress) , . ( , , ), . , . , , LoginStatus, , .

Answer 2

select [password], username, email, ...
from users 
where username=@username;

, . . , - , - ? , - (, ) ? ?

" ", . - , , , , , .

Answer 3

0 SQL, .

tsql datarow.

, , - :

create procedure [dbo].[sp_users_login] ( 
  @username varchar(30), 
  @password varchar(30), 
  @ret int output) 
as 
 if exists (select username from users where username=@username) 
 begin
   if exists (select [password], username  
                from users  
                where [password]=@password  
                  and username=@username) 
     set @ret =1 
   else  
     begin
         set @ret=2
     end  
   -- The select below is just an example - modify it to your needs
   select * from users 
   where username = @username
 end
 else  
   set @ret=3 

, , int.

:

• SQL- ,
• .
• , , , .

Answer 4

• DB- ( , , - ). T-SQL hashbytes -
• , , , , .
• , , - , IIRC

, ( , , - , ( , , , )):

create procedure [dbo].[sp_users_login] (
  @username varchar(30),
  @password varchar(30)
)
as
    select username -- , addition users.columns 
    from users 
    where pwhash = HashBytes('SHA1', UPPER(@username) + @password) 
        and username = @username)

:

?

, , noob?

Salt and Open Source Software

Protected hash and salt for PHP passwords