Need a stored procedure that inserts a row and returns the ID

Question

I tried to write a stored procedure that first inserts a new record into table and then returned the id of this new record. I am not sure if it is the correct way and the best way to achieve this.

ALTER PROCEDURE dbo.spAddAsset ( @Name VARCHAR(500), @URL VARCHAR(2000) ) AS BEGIN Set NOCOUNT on;  Insert Into Assets (Name, URL) Values (@Name, @URL)  Declare @new_identity int;  SELECT @new_identity = SCOPE_IDENTITY()  return @new_identity; END 

Answer 1

To return a single scalar value to the caller you should use an OUTPUT parameter, not RETURN. RETURN is for error/status codes. Also the prefix sp is redundant and unnecessary.

CREATE PROCEDURE dbo.AddAsset   @Name VARCHAR(500),   @URL  VARCHAR(2000),   @new_identity INT = NULL OUTPUT AS BEGIN     SET NOCOUNT ON;      INSERT dbo.Assets(Name, URL) SELECT @Name, @URL;     SET @new_identity = SCOPE_IDENTITY(); END GO 

Then to call it:

DECLARE @new_identity INT; EXEC dbo.AddAsset @Name = 'a', @URL = 'b', @new_identity = @new_identity OUTPUT; PRINT @new_identity; 

EDIT just adding a disclaimer that won't affect the asker in this specific scenario, but may help in other scenarios or for future readers. In SQL Server 2008 R2 and earlier, there is a potentially nasty bug with built-in functions such as SCOPE_IDENTITY when parallelism is used to derive the results to be inserted (think INSERT FROM othertable). This bug (here is the Connect item) is fixed in Cumulative Update #5 for SQL Server 2008 R2 SP1, but so far a fix has not appeared for 2008 R2 RTM, 2008 or 2005.