how do I use the Identity_Scope as a value to insert in table in SQL Server

Question

Ive looked over and over again for help but nothing has helped, I have a stored procedure where i want to insert the last identity ie SCOPE_IDENTITY() as B but it keeps returning a NULL value

CREATE PROCEDURE dbo.Createletter

@A INT,
@B NVARCHAR(50),
@C NVARCHAR(50),


AS

BEGIN;
    SET NOCOUNT ON;

    BEGIN;

    INSERT INTO dbo.Employees ( A, B, C )
    VALUES                     ( @A, SCOPE_IDENTITY(),@C);
    END;

END;
GO

Answer 1

If you have an identity column in your database table, when you add new row to your table, the identity value is generated automatically. It is possible to fetch the created value for the last Insert statement as follows.

create table dbo.Employees (
    A int identity(1,1), 
    B nvarchar(50), 
    C nvarchar(50)
)

declare @B nvarchar(50), @C nvarchar(50)
INSERT INTO dbo.Employees ( B, C ) VALUES ( @B, @C);

select SCOPE_IDENTITY()

If you procedure is creating a record in the table, you might be trying to get the IDENTITY for that row, I guess