Stored procedure - return identity as output parameter or scalar

Question

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    As an output parameter SET  RETURN VALUE   SCOPE IDENTITY   As a scalar SELECT SCOPE IDENTITY   Another way    Any pros cons to each

User · Answer

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

User · Answer

SELECT IDENT CURRENT  databasename dbo tablename   AS your identity column

User · Answer

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

User · Answer

Its all depend on your client data access-layer  Many ORM frameworks rely on explicitly querying the SCOPE IDENTITY during the insert operation    If you are in complete control over the data access layer then is arguably better to return SCOPE IDENTITY   as an output parameter  Wrapping the return in a result set adds unnecessary meta data overhead to describe the result set  and complicates the code to process the requests result   If you prefer a result set return  then again is arguable better to use the OUTPUT clause   INSERT INTO  MyTable  col1  col2  col3  OUTPUT INSERTED id  col1  col2  col3 VALUES   col1   col2   col3     This way you can get the entire inserted row back  including default and computed columns  and you get a result set containing one row for each row inserted  this working correctly with set oriented batch inserts   Overall  I can t see a single case when returning SCOPE IDENTITY   as a result set would be a good practice

User · Answer

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

[sql-server] Stored procedure - return identity as output parameter or scalar

Examples related to sql-server

Examples related to stored-procedures