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

"Doesn't seem to work" is a vague description of the issue. Do you get an error? Does it return no rows? If ID is not an `IDENTITY` then it is will be NULL. The very first step in debugging this would be to add a `PRINT @id`. That's just basic problem solving — Nick.Mc, Mar 17 '17 at 22:57
@Nick.McDermaid the values are being inserted but it is not returning any rows — Ishan Dragenox, Mar 18 '17 at 02:42
Assuming you mean the procedure returns no rows; yes that will happen because the column ID is null. Have you checked the table outside this stored procedure to confirm whether or not ID is NULL? Again, basic problem solving — Nick.Mc, Mar 18 '17 at 03:15
Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, Mar 18 '17 at 08:58

score 2 · Answer 1 · edited May 23 '17 at 12:25

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:

Sequences:

Just to confirm are **@Id** and **@id** same but just typos in your code snippet? And will this hold true even if my primary key isn't identity? — Ishan Dragenox, Mar 17 '17 at 22:16

Select inserted row in SQL Server stored procedure

1 Answers1