why can't I get the output param value? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

why can't I get the output param value?

Author

Topic

PGG123
Yak Posting Veteran

55 Posts

Posted - 2004-06-18 : 18:58:21

This is my stored proc:
CREATE PROCEDURE [dbo].[AllocateCodes]
@costcenter varchar(100),
@icn varchar(12),
@codes varchar(150),
@separator varchar(2),
@newcostcenter varchar(150) OUTPUT

AS

SET NOCOUNT ON

DECLARE @sql varchar(1000), @segcount smallint, @counter smallint, @CCLength smallint, @code_value varchar(50), @separator_pos smallint,
@start smallint, @length smallint, @find varchar(80), @modifiedCC varchar(150), @fullCC varchar(150),
@prevStart smallint, @prevLength smallint

SET @CCLength = 80

CREATE TABLE #AcctDist2 (
ind int IDENTITY PRIMARY KEY,
lngStartPos smallint,
lngLength smallint
)

--GET THE ACCT DIST STRUCTURE
SET @sql = 'INSERT Into #AcctDist2 (lngStartPos, lngLength)
Select lngStartPos, lngLength FROM Prof' + @icn + '.dbo.AccountDist where lngAccountLevel > 10'
EXEC (@sql)

--TOTAL ROWCOUNT OF #AcctDist1 TABLE
SELECT @segcount = COUNT(*) FROM #AcctDist2
SELECT @counter = 1
SELECT @codes = @codes + @separator
SET @fullCC = @costcenter --+ SPACE(@CCLength - LEN(@costcenter))

IF @segcount > 1
BEGIN
while @segcount >= @counter
BEGIN
Select @separator_pos = PATINDEX ( '%' + @separator + '%', @codes )
Select @code_value = LEFT ( @codes, @separator_pos - 1 )
Select @start = lngStartPos, @length = lngLength From #AcctDist2 Where ind = @counter

If @start = 0
SET @newcostcenter = @code_value + SPACE(@CCLength - LEN(@code_value))
Else
Begin
--pad code with spaces for the required length
--select @length as length
SET @modifiedCC = @code_value + SPACE(@length - LEN(@code_value))
--select @modifiedcc as modifiedcc
select @find = RTRIM(SUBSTRING(@fullCC, @start, @length))
--select @find as find

if @start <> (@prevStart + @prevlength)
SET @fullCC = @fullCC + SPACE(@start - (@prevStart + @prevLength))

if @find = ''
SET @fullCC = @fullCC + @modifiedCC
else
SET @fullCC = REPLACE(@fullCC ,@find, @modifiedCC)
--select @fullcc as fullcc
End

SELECT @prevStart = @start, @prevLength = @length
SET @counter = @counter + 1
SELECT @codes = STUFF ( @codes, 1, @separator_pos, '' )
END
SET @newcostcenter = @fullCC
END
ELSE
BEGIN
SET @codes = REPLACE(@codes, @separator, '')
SET @newcostcenter = @codes + SPACE(@CCLength - LEN(@codes))
END
--SELECT @newcostcenter
DROP TABLE #AcctDist2
SET NOCOUNT OFF
GO

Now, I execute this stored proc using this:
DECLARE @output varchar(150), ... (input params are declared here)
EXECUTE dbo.AllocateCodes @costcenter, @icn, @codes, @separator, @newcostcenter = @output OUTPUT
Select TheOutput = @output

This gives me NULL. But when I copy the body of my stored proc on Query Analyzer, run, and do Select @newcostcenter, I get the value I want.

I know I'm using the syntax to call the output param correctly, but why don't I get the value when I execute the stored proc?

Pls help. Thanks.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-18 : 19:05:35

--Well, you need to execute like this.
EXECUTE dbo.AllocateCodes @costcenter, @icn, @codes, @separator, @newcostcenter OUTPUT

--What's this?????
Select TheOutput = @newcostcenter

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

PGG123
Yak Posting Veteran

55 Posts

Posted - 2004-06-19 : 01:13:09

I'm saving the value of the output @newcostcenter param to a local variable @output and selecting it as TheOutput.

Kristen
Test

22859 Posts

Posted - 2004-06-19 : 03:09:41

quote:
Originally posted by derrickleggett

--What's this?????
Select TheOutput = @newcostcenter

Same as
SELECT @newcostcenter AS TheOutput

Or is this a trick question?

Kristen

Kristen
Test

22859 Posts

Posted - 2004-06-19 : 03:19:33

quote:
Originally posted by PGG123

This gives me NULL. But when I copy the body of my stored proc on Query Analyzer, run, and do Select @newcostcenter, I get the value I want.

When this happens to me I call the SProc itself, rather than putting the body in QA.

This makes sure that you are testing in QA with the same parameters as the Application.

If the parameters are not certain then use PROFILER to check what actual parameters the application its sending, or log them in a short-life table [e.g. if the SProc is being called from other SProcs]

I tend to add a debug parameter to my SProcs in this type of instance


CREATE PROCEDURE [dbo].[AllocateCodes] 
@costcenter varchar(100),
@icn varchar(12),
@codes varchar(150),
@separator varchar(2),
@newcostcenter varchar(150) OUTPUT
,@intDebug int=0

and then change
--SELECT @newcostcenter
to
IF @intDebug >= 1 SELECT @newcostcenter
so you can easily test an SProc from QA by adding ",@intDebug=1" to the EXEC command.

Actually, I litter the SProc with
IF @intDebug >= 1 SELECT [MySprocName]='DEBUG(1)', [@newcostcenter]=@newcostcenter ...

Kristen

nr
SQLTeam MVY

12543 Posts

Posted - 2004-06-19 : 13:57:54

Put Select @newcostcenter into the SP and run your exec. You should get the same value returned in the output parameter as in the resutset.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources