set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[checkLogin]
	-- Add the parameters for the stored procedure here
	@login nvarchar(255),
	@password nvarchar(255)

AS
BEGIN

	SET NOCOUNT ON;

	declare @user int;
	declare @exists int;

	SELECT @user = member_id from Table1
	where User_login = @login	
    and User_pwd = @password
	

	select @exists = member_deleted from Table2 where member_id = @userId
	if @exists = 1
		return -1
	else
		select top 1 isnull(@exists,-1) from User

END

quote:

---

Originally posted by d4046949

Hey guys

I’m having some trouble getting some code to work.

I’ve written the following:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[checkLogin]
	-- Add the parameters for the stored procedure here
	@login nvarchar(255),
	@password nvarchar(255)

AS
BEGIN

	SET NOCOUNT ON;

	declare @user int;
	declare @exists int;

	SELECT @user = member_id from Table1
	where User_login = @login	
    and User_pwd = @password
	

	select @exists = member_deleted from Table2 where member_id = @userId
	if @exists = 1
		return -1
	else
		select top 1 isnull(@exists,-1) from User

END

Basically its meant to do two things: If a user enters a nonexistent username and password then the system returns -1. However if that account has been deleted – ie a Boolean flag set to true – it should also return -1. The problem is that this is in a different table.


Table 1 – where username and password are stored
Table 2 – where a little more info is stored + accountDisabled/enabled.

Any ideas…

---

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[checkLogin]
	-- Add the parameters for the stored procedure here
	@login nvarchar(255),
	@password nvarchar(255)

AS
BEGIN

	SET NOCOUNT ON;
	
select isnull(nullif(member_deleted,1),-1) 
from Table2 t2
join Table1 t1
on t1.member_id=t2.member_id 
where t1.User_login = @login	
and t1.User_pwd = @password
	
END

declare @table1 table (user_login varchar(50),user_pwd varchar(50),member_id int)
insert into @table1
select 'test name','pass',1 union all
select 'a n other','pass2',2

declare @table2 table (member_id int, member_deleted bit)
insert into @table2
select 1,1 union all
select 2,0

declare @login varchar(50), @password varchar(50)

set @login = 'x'
set @password = 'c'
--set @login = 'test name'
--set @password = 'pass'


declare @result int

select @result= member_deleted*-1 
from @Table1 t1
join @Table2 t2
on t1.member_id=t2.member_id 
where t1.User_login = @login	
and t1.User_pwd = @password

select coalesce(@result,-1)