Output Parameter Issue - 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

SQL Server Development (2000)

Output Parameter Issue

Author

Topic

fpendino
Starting Member

5 Posts

Posted - 2009-11-03 : 12:20:23

Hi,
I have a stored proc which I'm using for a user login and it contains output parameters. I can not retreive the output paramters.

This is just a test retrieval but here is what I have.

DECLARE @curSession varchar(100)
DECLARE @ERR varchar(300)
DECLARE @DONE bit

exec spLoginUser 'TESTUser', 'TestPass', '00', '123', @ERR, @DONE, @CurSession

Select @curSession, @ERR, @DONE

The varibles are supposed to retrive the output parameters. They display NULL. I've tried setting the output parameters by using SELECT @OutputParam and SET @OutputParam..

I know it is assigning the value to all output parameters because when I do a select from within the spLoginsUser Stored Proc, it does output the correct info. Just not when I try to retrieve the parameters when calling from another stored proc(like above). I'm using SQL 2000 and here are bits of the SQL code from spLoginUser.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[spLoginUser]
-- Add the parameters for the stored procedure here
(@UserName varchar(10),
@Password varchar(20),
@IPAddress varchar(20),
@SessionID varchar(100),
@ErrorString varchar(100) output,
@Success bit output,
@CurrentSession varchar(100) output
)

AS
BEGIN

SET NOCOUNT ON;

DECLARE @UserActive bit
DECLARE @WebApplicationUserID int
DECLARE @PasswordVerified bit
DECLARE @CurrentSessionID varchar(100)

--Check to see if user exists and is active.. Removed code for this

IF @UserActive = 1
BEGIN
IF @PasswordVerified = 1
BEGIN
SET @Success = 1
SET @CurrentSession = 'TEST1234'
END
END

SELECT @Success, @CurrentSession --This returns a set of the correct data

END

That is basically the idea of what I am doing, minus some of the bits of code..

fpendino
Starting Member

5 Posts

Posted - 2009-11-03 : 12:21:58

Is there something I have to do to enable Output Parameters?

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-11-03 : 12:47:07

[code]
exec spLoginUser 'TESTUser', 'TestPass', '00', '123', @ERR OUTPUT, @DONE OUTPUT, @CurSession OUTPUT
[/code]

fpendino
Starting Member

5 Posts

Posted - 2009-11-03 : 13:03:48

Thank You! I did figure that out finally but I'm trying to retrieve these values in ASP.net and I think it is a separate issue. I was using this for testing purposes though.

Thanks Again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources