T-SQL stored procedures

Question

I have a table Users with the following columns:

  Userid   Username    Password
     1     Username1   Password1
     2     Username2   Password2
     3     Username3   Password3

There is another table, MappingRoles:

 Userid   RoleId
     1       1 (FinanceUser)
     1       2 (Agent)
     1       3 (Client)

I want to write a stored procedure which should return two columns

IsValidUser
Roles -- of the user

This is my attempt:

CREATE PROCEDURE  getRoles
    @Username varchar(50), 
    @Password varchar(50) 
AS
BEGIN
    Select * from Users as x inner join MappingRoles as y 
    on x.Userid=y.Userid 
    where x.Userid=(Select Userid from Users 
      where UserName='Username1' and Password='Password1');
END
GO

I am expecting optimization in this query to provide 2 return values:

• IsUserAuthenticated - (the inner Select from Users)

• If it's present then all the values.

Any suggestion would be highly appreciated.

Answer 1

Try a simpler join, drop the sub query:

CREATE PROCEDURE  getRoles
    @Username varchar(50), 
    @Password varchar(50) 
AS
BEGIN
    Select 
        * 
    from Users as us 
    inner join MappingRoles mr 
    on us.Userid=mr.Userid 
    where us.UserName=@username and us.Password=@password);
END
GO