SQL Question

[Rob Willaar]

Hi all,

I like to increment a number field in a database and return this value at
the same time. Can this be done with sql?

tnx for any help

 

[Guest]

Is it an IDENTITY field. If so, you can use SCOPE_IDENTITY to get the value
of the field. If you are simply incrementing via an UPDATE, you will have to
grab the value and output yourself. Something like:

CREATE PROCEDURE IncrementValue
(
@RecordID int
)
AS

DECLARE @IncrementedValue int

SELECT @IncrementedValue=IncrementedValue + 1
FROM MyTable
WHERE RecordID = @RecordID

UPDATE MyTable SET IncrementedValue = @IncrementedValue
WHERE RecordID = @RecordID

SELECT @IncrementedValue
GO

This is a simple return. You can also use an OUTPUT parameter on the sproc.


---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************