Newbie Stored Proc Question - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Newbie Stored Proc Question

Author

Topic

evanburen
Posting Yak Master

167 Posts

Posted - 2005-09-18 : 19:59:27

This procedure is accurately returning the correct output parameter but it isn't returning any records. I need both and I'm not sure this isn't working as expected. Thanks.

CREATE Procedure sp_Login
@login as nvarchar(50),
@password as nvarchar(50),
@ValidUser Bit OUTPUT

AS

IF EXISTS (
SELECT *
FROM tblUsers
WHERE login = @login AND password = @password)
SET @ValidUser = 1
ELSE
SET @ValidUser = 0
GO

scullee
Posting Yak Master

103 Posts

Posted - 2005-09-18 : 21:01:49

Because the if exists doesnt actually return data. You either have to repeat the select inside if statement, or always return the param and if you get a record return assume validUser = 1 and if an empty recordset is returned then validUser = 0

Like this


CREATE Procedure sp_Login
@login as nvarchar(50),
@password as nvarchar(50)
AS

SELECT * FROM tblUsers
WHERE login = @login AND password = @password

GO

Kristen
Test

22859 Posts

Posted - 2005-09-19 : 05:12:23

"I need both"

Perhaps this is what you are after?


CREATE Procedure sp_Login
	@login as nvarchar(50), 
	@password as nvarchar(50),
	@ValidUser Bit OUTPUT

AS
	
IF EXISTS (
          SELECT *
            FROM tblUsers
          WHERE login = @login AND password = @password)
BEGIN
       SET @ValidUser = 1

	SELECT *
	FROM tblUsers
	WHERE login = @login AND password = @password
END
ELSE
      SET @ValidUser = 0
GO

Kristen

evanburen
Posting Yak Master

167 Posts

Posted - 2005-09-19 : 09:51:57

This is returning 0/false whether a user is found or not. Thanks.

Kristen
Test

22859 Posts

Posted - 2005-09-19 : 13:21:01

"This is returning 0/false whether a user is found or not"

I doubt that, I expect it is return an unreliable value.

So you want an OUTPUT variable, a RECORDSET and a RETURN value?


CREATE Procedure sp_Login
	@login as nvarchar(50), 
	@password as nvarchar(50),
	@ValidUser Bit OUTPUT

AS
	
IF EXISTS (
          SELECT *
            FROM tblUsers
          WHERE login = @login AND password = @password)
BEGIN
       SET @ValidUser = 1
	SELECT *
	FROM tblUsers
	WHERE login = @login AND password = @password
END
ELSE
BEGIN
      SET @ValidUser = 0
END
RETURN @ValidUser
GO

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources