Select inserted row in SQL Server stored procedure

Question

I am inserting some values into a table using the INSERT command then I am getting it's inserted value as OUTPUT. Now I want to select the inserted row by its id using the SELECT command I am using the following code but it doesn't seem to work.

CREATE PROCEDURE [dbo].[SP_UserRegistration]
(
    @Name VARCHAR(100),
    @Contact VARCHAR(20),
    @DOB VARCHAR(20),
    @MailAddress VARCHAR(500),
)
AS
BEGIN
    BEGIN TRY

    DECLARE @id INT

    INSERT INTO Customer (Name, Contact, DOB, MailAddress)
    OUTPUT inserted.ID INTO @id
    VALUES (@Name, @Contact, @DOB, @MailAddress)

    SELECT * 
    FROM Customer
    WHERE ID = @id

    END TRY
    BEGIN CATCH
        PRINT('Error in SP_UserRegistration')
    END CATCH
END

Answer 1

You could also use an output parameter instead of select to return the rows back to your application.

If your Id is generated by a sequence, use next value for:

create procedure [dbo].[usp_UserRegistration] (
    @Name varchar(100),
    @Contact varchar(20),
    @dob varchar(20),
    @MailAddress varchar(500),
    @Id int output
) as
begin;
set nocount, xact_abort on;
  begin try;
    begin tran
      /* your critiera for a new record here */
      select  @Id = Id
        from  dbo.Customer with (updlock, serializable)
        where Name = @Name
          and dob = @dob;
      if @@rowcount = 0
      begin;
        set @Id = next value for dbo.IdSequence /* with your Sequence name here */
        insert into dbo.Customer (Id, Name, Contact, dob, MailAddress)
        values (@Id, @Name, @Contact, @dob, @MailAddress ); 
      end;
    commit tran;
  end try
  begin catch;
    if @@trancount > 0 
      begin;
        rollback transaction;
        throw;
      end;
  end catch;
go

If your Id is an identity column, use scope_identity().

There is a big difference between @@identity, scope_identity(), and ident_current().

References:

• Why you should always set nocount, xact_abort on;
• Error handling - Erland Sommarskog, using throw

Sequences:

• Sequence Numbers - msdn
• create sequence - msdn
• next value for - msdn
• sp_sequence_get_range - msdn
• Simple Talk - Sql Server sequence basics - Joe Celko