When you insert a record into a table with an identity column, you can use SCOPE_IDENTITY() to get that value. Within the context of a stored procedure, which would be the recommended way to return the identity value:
SET @RETURN_VALUE = SCOPE_IDENTITY()
SELECT SCOPE_IDENTITY()
Any pros/cons to each?
This question is related to
sql-server
stored-procedures
I prefer to return the identity value as an output parameter. The result of the SP should indicate whether it succeeded or not. A value of 0 indicates the SP successfully completed, a non-zero value indicates an error. Also, if you ever need to make a change and return an additional value from the SP you don't need to make any changes other than adding an additional output parameter.
SELECT IDENT_CURRENT('databasename.dbo.tablename') AS your identity column;
Another option would be as the return value for the stored procedure (I don't suggest this though, as that's usually best for error values).
I've included it as both when it's inserting a single row in cases where the stored procedure was being consumed by both other SQL procedures and a front-end which couldn't work with OUTPUT parameters (IBATIS in .NET I believe):
CREATE PROCEDURE My_Insert
@col1 VARCHAR(20),
@new_identity INT OUTPUT
AS
BEGIN
SET NOCOUNT ON
INSERT INTO My_Table (col1)
VALUES (@col1)
SELECT @new_identity = SCOPE_IDENTITY()
SELECT @new_identity AS id
RETURN
END
The output parameter is easier to work with in T-SQL when calling from other stored procedures IMO, but some programming languages have poor or no support for output parameters and work better with result sets.
Either as recordset or output parameter. The latter has less overhead and I'd tend to use that rather than a single column/row recordset.
If I expected to >1 row I'd use the OUTPUT clause and a recordset
Return values would normally be used for error handling.
Source: Stackoverflow.com