SA Developer .NET

Say I've got a stored procedure to insert a record into the following table:

CREATE TABLE [Foo] (
  [ID]  UNIQUEIDENTIFIER NOT NULL PRIMARY KEY DEFAULT NEWID(),
  [C1] INTEGER NOT NULL
);
GO

I'd typically declare the stored procedure along the following lines:

Approach 1: 

CREATE [Foo_Insert] (
  @ID UNIQUEIDENTIFIER = NULL OUTPUT,
  @C1 INTEGER
)
AS
BEGIN
  IF (@ID IS NULL)
    SET @ID = NEWID();

  INSERT INTO [Foo] ([ID], [C1]) VALUES (@ID, @C1);
END
GO

Which I'm pretty sure would be technically ok but it includes two statements that need to be executed and I was wondering if a better approach would be to use the OUTPUT INTO clause and let the default clause do the work.

Approach 2: 

CREATE [Foo_Insert] (
  @ID UNIQUEIDENTIFIER = NULL OUTPUT,
  @C1 INTEGER
)
AS
BEGIN
  DECLARE @Output TABLE ([ID] UNIQUEIDENTIFIER NOT NULL);
  INSERT INTO [Foo] ([ID], [C1]) OUTPUT INSERTED.[ID] INTO @Output VALUES (@ID, @C1);
  SELECT TOP 1 @ID = [ID] FROM @Output;
END
GO

Which of the above approaches is the better approach?

In addition I could also rework the stored procedure interface to not use OUTPUT parameters at all and do the following:

Approach 3: 

CREATE [Foo_Insert] (
  @ID UNIQUEIDENTIFIER = NULL,
  @C1 INTEGER
)
AS
BEGIN
  INSERT INTO [Foo] ([ID], [C1]) OUTPUT INSERTED.* VALUES (@ID, @C1);
END
GO

Approach 3 seems the simplest as it effectively acts like an insert and select back statement all in one.

I'd appreciate everyone's opinion and experience on the above 3 approaches and the pros and cons of each.

I like approach 1 best, because the other two don't work.

Defaults apply when you don't insert a value, not when you explicitly insert NULL. Both your second and third options throw the following error if @ID is not passed

EXEC  Foo_Insert2 @C1 = 3

Msg 515, Level 16, State 2, Procedure Foo_Insert, Line 9
Cannot insert the value NULL into column 'ID', table 'Testing.dbo.Foo'; column does not allow nulls. INSERT fails.

EXEC  Foo_Insert3 @C1 = 3

Msg 515, Level 16, State 2, Procedure Foo_Insert3, Line 8
Cannot insert the value NULL into column 'ID', table 'Testing.dbo.Foo'; column does not allow nulls. INSERT fails.  

Doh! In future I should test my examples out. I was actually using the following form in my real code which works:

INSERT INTO [Foo] ([ID], [C1]) OUTPUT INSERTED.[ID] INTO @Output SELECT ISNULL(@ID, NEWID()), @C1;

The reason why, well I was doing some selects in the FROM clause to validate the supplied data.

With that, I like 3 better, because it's cleaner. I don't think you'll see any real changes in the way it runs.