Returning Values from Stored Procedures

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)

Returning Values from Stored Procedures

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-06-24 : 01:26:09

Shane writes "I need to create a stored procedure that I can call from ASP, that will increment a field in database by 1, then return the updated value to the procedure that called it.

Is this possible???

Thanks in advance for even reading this.

Cheers,

Shane."

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2002-06-24 : 04:35:55

You need a OUTPUT parameter, like so:

CREATE PROCEDURE up_IncrementField
(@UpdateID as int,
@ReturnID as int OUTPUT)
AS
UPDATE tblToInc
SET fldA = fldA + 1
WHERE ID = @UpdateID

SELECT @ReturnID = fldA FROM tblToInc WHERE ID = @UpdateID

GO

macka
Posting Yak Master

162 Posts

Posted - 2002-06-24 : 04:52:58

If the value passed in relates to the primary key (or another unique constraint) then you can also achieve this in just one statement. Using YellowBugs code:

CREATE PROCEDURE up_IncrementField
(@UpdateID as int,
@ReturnID as int OUTPUT)
AS
UPDATE tblToInc
SET @ReturnID = fldA = fldA + 1 <-------
WHERE ID = @UpdateID

This is covered in BOL.

macka.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources