Login authentication as Stored procedure?

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Author

Topic

monaya
Yak Posting Veteran

58 Posts

Posted - 2009-01-28 : 12:05:04

I'm trying to authenticate a login using a stored procedure. Basically what I want is to check an email and password, if the record matched I want to send a value to the caller like "success" and if not, another value. here's what I came up with but it's not working. I'm very new to T-SQL. Can anyone put in order for me? Thanks!

CREATE PROCEDURE [dbo].[sp_login]
@email VARCHAR(75),
@pwd VARCHAR(25),
@clientgroup VARCHAR(20) OUTPUT
@loginresults INTEGER OUTPUT

AS

IF
(SELECT emailaddress, userpwd FROM clients WHERE emailaddress = '@email' AND userpwd = '@pwd') > 0
BEGIN
DECLARE @loginresults = 1
END

ELSE

(SELECT emailaddress, userpwd FROM clients WHERE emailaddress = '@email' AND userpwd = '@pwd') < 1

BEGIN
DECLARE @loginresults = 0
END

GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 12:21:39

quote:
Originally posted by monaya

I'm trying to authenticate a login using a stored procedure. Basically what I want is to check an email and password, if the record matched I want to send a value to the caller like "success" and if not, another value. here's what I came up with but it's not working. I'm very new to T-SQL. Can anyone put in order for me? Thanks!

CREATE PROCEDURE [dbo].[sp_login]
@email VARCHAR(75),
@pwd VARCHAR(25),
@clientgroup VARCHAR(20) OUTPUT
@loginresults INTEGER OUTPUT

AS

IF
(SELECT COUNT(*) FROM clients WHERE emailaddress = @email AND userpwd = @pwd) > 0
BEGIN
SET @loginresults = 1
END

ELSE

IF (SELECT COUNT(*) FROM clients WHERE emailaddress = @email AND userpwd = @pwd ) < 1

BEGIN
SET @loginresults = 0
END

GO

modify like above and see

tonymorell10
Yak Posting Veteran

90 Posts

Posted - 2009-01-28 : 12:41:36

Here's a different variation for you:

CREATE PROCEDURE [dbo].[sp_login]
@email VARCHAR(75),
@pwd VARCHAR(25),
@clientgroup VARCHAR(20) OUTPUT
@loginresults INTEGER OUTPUT

AS

IF EXISTS (SELECT 1 FROM dbo.clients WHERE emailaddress = @email AND userpwd = @pwd) BEGIN
SELECT @loginresults = 1
END
ELSE BEGIN
SELECT @loginresults = 0
END
GO

monaya
Yak Posting Veteran

58 Posts

Posted - 2009-01-28 : 16:16:11

Thanks guys. With both code blocks I get an error:

Must declare the scalar variable "@loginresults"

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-28 : 18:57:27

Donot create store proc with sp_...

quote:
Originally posted by tonymorell10

Here's a different variation for you:

CREATE PROCEDURE [dbo].[usp_login]
@email VARCHAR(75),
@pwd VARCHAR(25),
@clientgroup VARCHAR(20) OUTPUT,
@loginresults INTEGER OUTPUT

AS

IF EXISTS (SELECT 1 FROM dbo.clients WHERE emailaddress = @email AND userpwd = @pwd) BEGIN
SELECT @loginresults = 1
END
ELSE BEGIN
SELECT @loginresults = 0
END
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources