SQL Insert Procedure

Question

Why isn't my insert procedure returning the ID of the newly inserted row? AND, when calling this procedure, why do I have to supply a value for @EventId? That column is a PK with IDENTITY.

IF OBJECT_ID ( 'vjsql.EventsINSERT', 'P') IS NOT NULL
    DROP PROCEDURE EventsINSERT 
GO
CREATE PROCEDURE EventsINSERT
    @EventId      int  OUTPUT,
    @EventDate         datetime,
    @Title    varchar(100),
    @IsActive     bit
AS
BEGIN
    INSERT INTO EventCalendar ( EventDate, Title, IsActive)
    VALUES ( @EventDate, @Title, @IsActive)
    SELECT @EventId = SCOPE_IDENTITY()
END

Answer 1

I'm pretty rusty with tsql, but don't you need to explicitly select @@identity to get that row id? That's where i'd go digging as I think scope_identity() may not return a value in the context of a user function/procedure.

Answer 2

Try adding some statement terminators:

BEGIN
INSERT INTO EventCalendar ( EventDate, Title, IsActive)
VALUES ( @EventDate, @Title, @IsActive);

SELECT @EventId = SCOPE_IDENTITY();
END

AND, when calling this procedure, why do I have to supply a value for @EventId? That column is a PK with IDENTITY.

You don't, but you do need to supply a variable of type int (or compatible with int) for the output value to be put into.

Answer 3

How are you making a call to the stored procedure?

This SP is returning the value of EventID by means of using OUTPUT parameters.
i.e. In programming terms, this is a procedure (not a function) that accepts an OUTPUT parameter which will be set with the value during the execution of the stored procedure.

For this, you will have to pass the variable for @EventID. The value of which will be set within the procedure and you will be able to read the value of it, once the procedure has finished.

See the example code below.

DECLARE @NewEventID INT

EXEC EventsINSERT
  @EventId = @NewEventID OUTPUT,
  @EventDate = '08/04/09',
  @Title     = 'Hello World',
  @IsActive  = 0

SELECT @NewEventID

Answer 4

You don't need to specify a value for the OUTPUT parameter, you need to specify which local variable the output gets put into:

By default, SQL Management Studio names the parameter and the variable the same, which can be confusing. Here's an example of your SP being called:

DECLARE @InsertedEventId int

EXEC    [dbo].[EventsINSERT]
        @EventId = @InsertedEventId OUTPUT,
        @EventDate = N'2009-08-05',
        @Title = N'Some event',
        @IsActive = 1

-- Display ID as result set
SELECT @InsertedEventId

Just to clarify: your stored procedure is fine. I used it as-is.

Answer 5

Why isn't my insert procedure returning the ID of the newly inserted row?

Your code should work. Try in the console instead of

SELECT @EventId = SCOPE_IDENTITY()

doing

SELECT SCOPE_IDENTITY()

and view what happens. Is possible that you are calling it the wrong way. You should store the value of the OUTPUT variable in a variable in the scope where you call this SP.

when calling this procedure, why do I have to supply a value for @EventId?

Because you have to supply a value for every parameter you have. It doesn't matter if is a real value, it will be discarded, but you must call the stored procedure with a variable in this parameter to catch the returned value.