Insert query return SCOPE_IDENTITY()

This is a example of how to get [table]ID back when you inserted a record to table.

this is very useful when you needed to do more business logic after table inserting.


ALTER PROCEDURE [dbo].[uspNewsletterSubscriptions_Insert]

@TitleId int,
@Forename nvarchar(255),
@Surname nvarchar(255),
@EmailAddress nvarchar(255),
@Id int OUTPUT

AS
BEGIN

insert into tNewsletterSubscriptions(TitleId, Forename, Surname, EmailAddress)
values(@TitleId, @Forename, @Surname, @EmailAddress)


SET @Id = SCOPE_IDENTITY()

SELECT [Id]
,[Created]
,[LastUpdated]
,[TitleId]
,[Forename]
,[Surname]
,[EmailAddress]

FROM tNewsletterSubscriptions
WHERE Id = SCOPE_IDENTITY()

END