OUTPUT Clause with Insert in Stored Procedure in SQL Server.

When you need Newly added Records ID from SP. Normally we tend to use @@Identity. Now this has Scope related issues.  To get Perfect Value for Inserted Record. We Can use OUTPUT INSERTED

 SQL

  

GO

/****** Object:  Table [dbo].[Table_1]    Script Date: 01/02/16 16:48:21 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Table_1](

       [ID] [int] IDENTITY(1,1) NOT NULL,

       [Name] [varchar](50) NOT NULL,

 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED

(

       [ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

  

Create Proc spInsertRecord @name as Varchar(50), @NewID as int Output

as

begin

DECLARE @Tbl TABLE (IdentityColumn BIGINT);  

INSERT INTO [dbo].[Table_1]  ([Name])

     OUTPUT inserted.ID INTO @Tbl

     VALUES (@name)

End

SELECT @NewID = IdentityColumn FROM @Tbl         

GO

Declare @NewID as int

Exec spInsertRecord 'ABC', @NewID = @NewID Output

Select @NewID

Select * From [Table_1]

USAGE IN Front End

Dim sqlCMD As New SqlCommand

sqlCMD.Connection = cnACFA

sqlCMD.CommandType = CommandType.StoredProcedure

sqlCMD.Transaction = SQLtrn

sqlCMD.CommandText = "spInsertRecord"

sqlCMD.Parameters.Add("@name", SqlDbType.VarChar).Value = 'XYZ'

sqlCMD.Parameters("@NewID").Direction = ParameterDirection.Output

sqlCMD.ExecuteNonQuery()

'Get newly added Records ID

Dim intSONewSRNO As Integer = sqlCMD.Parameters("@NewID ").Value