stored procedure and output parameters

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

General SQL Server Forums

New to SQL Server Programming

stored procedure and output parameters

Author

Topic

Sean_B
Posting Yak Master

111 Posts

Posted - 2007-03-23 : 10:10:03

Hi,

I hope someone can help.

I've written a stored procedure that returns a value via an output parameter.

I'm calling the stored procedure in an sql session is a loop, and it passes the value back correctly the first time, but all subsequent calls the output parameter appears to have the same value. I believe that I'm making some very basic mistake, but I can't work it out.

Here's how I'm calling the stored procedure several times
begin
declare @instrumentid int
exec GetInstrumentId 'OFX,AUDCHF,p,1,2007-03-20 16:54:21.843,2007-06-20,100.1', @instrumentid output;
select @instrumentid
exec GetInstrumentId 'OFX,AUDUSD,c,2,2007-03-20 16:54:21.843,2007-06-20,100.2', @instrumentid output;
select @instrumentid
exec GetInstrumentId 'OFX,AUDCHF,p,3,2007-03-20 16:54:21.843,2007-06-20,100.3', @instrumentid output;
select @instrumentid
end

And this is the start of the stored procedure

Create PROCEDURE [dbo].[GetInstrumentId]
(@Ticket varchar(250), @InstrumentId int output)
AS

If I call the stored procedure once it gives the corect output, if I call it several times the output parameter (@instrumentid ) never changes.

Sean

nr
SQLTeam MVY

12543 Posts

Posted - 2007-03-23 : 10:11:35

Looks ok.
Try
declare @instrumentid int
exec GetInstrumentId 'OFX,AUDCHF,p,1,2007-03-20 16:54:21.843,2007-06-20,100.1', @instrumentid output;
select @instrumentid
select @instrumentid = null
exec GetInstrumentId 'OFX,AUDUSD,c,2,2007-03-20 16:54:21.843,2007-06-20,100.2', @instrumentid output;
select @instrumentid
select @instrumentid = null
exec GetInstrumentId 'OFX,AUDCHF,p,3,2007-03-20 16:54:21.843,2007-06-20,100.3', @instrumentid output;
select @instrumentid

==========================================
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.

Sean_B
Posting Yak Master

111 Posts

Posted - 2007-03-23 : 10:16:39

Thanks a lot, setting the parameter to null does the trick.

Sean

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-23 : 11:30:28

Huh?


USE Northwind
GO

CREATE PROC mySproc99
	@x int, @y char(1) OUTPUT
AS

IF @x = 1 SET @y = 'a'
IF @x = 2 SET @y = 'b'
IF @x = 3 SET @y = 'c'

RETURN
GO
DECLARE @y char(1)

EXEC mySproc99 1, @y OUTPUT
SELECT @y
EXEC mySproc99 2, @y OUTPUT
SELECT @y
EXEC mySproc99 3, @y OUTPUT
SELECT @y
GO

DROP PROC mySproc99
GO

You have something else going on

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources