How to create login procedure based on roleid from multiple table

Question

0.00/5 (No votes)

See more:

SQL-Server-2014

how to create login procedure based on roleid from multiple table

I have two table with me
table-1 tblTeacher
----------------------------------------------------
Id Name Email Password RoleId Status
----------------------------------------------------
1 aaa aaaa 123 1 0
2 bbb bbbb 1234 2 1
3 ccc cccc 1234 1 1

table-2 tblStudent
----------------------------------------------------
Id Name Email Password RoleId Status
----------------------------------------------------
1 xxx xxxx 123 3 1
2 yyy yyyy 1234 3 0

i want to login based on the role id and if status is true(1)

What I have tried:

SQL

create procedure spLoginn(
@Email varchar(50),
@Pasword varchar(20),
@RId int out
)
as begin
  --declare @Rid int
  if @Email is not null
     if @Pasword is not null
	   set @Rid=(Select RoleId from tblTeacher where Email=@Email and Pasword=@Pasword)
	 if @Rid=0
	   set @Rid=(Select RoleId from tblStudent where Email=@Email and Pasword=@Pasword)
	   end

Posted 26-Sep-17 22:36pm

Member 12611488

Updated 27-Sep-17 0:06am

Karthik_Mahalingam

v2

Add a Solution

Comments

Richard Deeming 27-Sep-17 12:15pm

I appreciate this is probably just a school project, but in any real-world application, you should NEVER store passwords in plain text.

Secure Password Authentication Explained Simply[^]
Salted Password Hashing - Doing it Right[^]

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Karthik_Mahalingam · Accepted Answer · 2017-09-27T00:06:00

try

alter procedure spLoginn(
@Email varchar(50),
@Pasword varchar(20),
@RId int out
)
as BEGIN

  
  if ( @Email is not null and  @Pasword is not null ) 
		begin
			   set @Rid=(Select RoleId from tblTeacher where Email=@Email and Pasword=@Pasword)
			 if @Rid is null
			   set @Rid=(Select RoleId from tblStudent where Email=@Email and Pasword=@Pasword)
	     end
  END

if @RId is null then the Email/Password combination is not present either table.