Stored procedure in SQL Server for backend-CodePudding

I'm beginner in SQL Server. I have two tables, User and User_role.

I send user name & password from ASP.NET Api to SQL Server.

I need a procedure that gets user name & pass from tbluser and returns PostId from tblRole.

I could do it with Entity Framework, but I should get it by a procedure and don't know anything about that.

Thanks a lot

id	username	password
10	person1	123
11	person1	123
12	person1	123

id	postId	userId	roleId
1	1	10	2
2	2	11	2
3	3	12	2

CodePudding user response：

If you want to just return the postId, you can try this:

CREATE PROCEDURE st_getPostIdFromRole(
       @username varchar(100), 
       @password varchar(100)
)
AS
BEGIN
   SELECT PostId
   FROM User_Role ur
   INNER JOIN Users u ON (u.Id = u.UserId)
   WHERE u.UserName = @username
   AND   u.Password = @password
END

PS:- I sincerely hope that this is just a learning project and you are not storing passwords as plaintext or hashing them without a salt.