Log in Stored Procedure Best Practice in SQL

Log in Stored Procedure Best Practice



(
@uname varchar(100),
@password varchar(100)
)
As
declare @pass varchar(100)
select @pass=password from tbl_user where user_name=@uname
if(@pass=@password)
begin
select user_id,role,type from tbl_user where user_name=@uname and password=@pass
end
else
begin
select 0 as user_id
end