Trying to write a stored procedure with a Identity column to create my table values

user_11962862 0
Viewed 192

I am trying to write a stored procedure that creates a new row of data in my table that has an Identity column. I can't figure out what I'm doing wrong to get it to store my data in the table.

Here is my table

CREATE TABLE [Promotional].[Proposals]
(
    [Proposal_Uid] [int] IDENTITY(1,1) NOT NULL,
    [Prime_Contract] [nvarchar](30) NULL,
    [Sub_Contract] [nvarchar](30) NULL,
    [Po_Id] [nvarchar](30) NULL,
    [Proposal_Title] [nvarchar](50) NULL,
    [Client_Name] [nvarchar](40) NULL,
    [Client_Code] [nvarchar](20) NULL,
    [Total_Proposal_Amount] [decimal](18, 2) NULL,
    [ODC_Amount] [decimal](18, 2) NULL,
    [Manager_Name] [nvarchar](30) NULL,
    [Admin] [nvarchar](30) NULL,
    [Due_Date] [date] NULL,
    [Start_Date] [date] NULL,
    [End_Date] [date] NULL,
    [Proposal_Number] [nvarchar](20) NULL,
    [Contract_Type] [nvarchar](16) NULL,

    CONSTRAINT [Proposal_Uid] 
        PRIMARY KEY CLUSTERED ([Proposal_Uid] 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

Here is my stored procedure:

ALTER PROCEDURE [Promotional].[Proposals_Create] 
    (@Prime_Contract nvarchar(30),
     @Sub_Contract nvarchar(30),
     @Po_Id nvarchar(30),
     @Proposal_Title nvarchar(50),
     @Client_Name nvarchar(40),
     @Client_Code nvarchar(20),
     @Total_Proposal_Amount decimal(18,2),
     @ODC_Amount decimal(18,2),
     @Manager_Name nvarchar(30),
     @Admin nvarchar(30),
     @Due_Date date,
     @Start_Date date,
     @End_Date date,
     @Proposal_Number nvarchar(20),
     @Contract_Type nvarchar(16),
     @NewId int output)
AS
BEGIN
    IF @NewId IS NULL
    BEGIN
        INSERT INTO Promotional.Proposals (Prime_Contract, Sub_Contract, Po_Id, Proposal_Title, Client_Name, Client_Code,
                                           Total_Proposal_Amount, ODC_Amount, Manager_Name, Admin, Due_Date, Start_Date, End_Date, Proposal_Number, Contract_Type)
        VALUES (@Prime_Contract, @Sub_Contract, @Po_Id, @Proposal_Number, @Client_Name, @Client_Code,
                @Total_Proposal_Amount, @ODC_Amount, @Manager_Name, @Admin, @Due_Date, @Start_Date, @End_Date,
                @Proposal_Number, @Contract_Type)

        SET @NewId = SCOPE_IDENTITY()
    END

    RETURN @NewId
END
GO

I know it's something small I missed. But, my OUTCOME is that when you run the procedure, it inserts the values in the correct columns, and the primary key is auto updated without having to stick your own value into the statement. My execution of the statement would be as follows

EXEC Promotional.Proposals_Create 'hj','fd','fd','fd','fd','fd','0.23','1.24','fd','fd','2020/08/30','2020/08/30','2020/08/30','fd','fd';

And I get this error:

Msg 201, Level 16, State 4, Procedure Promotional.Proposals_Create, Line 0 [Batch Start Line 0]
Procedure or function 'Proposals_Create' expects parameter '@NewId', which was not supplied.

I shouldn't have to provide that parameter because it should auto update with my PK which is Proposal_Uid. Or do I have to make my Proposal_Uid the value returned from ScopeIdentity?

sql-server sql
2 Answers

@NewID should not be a parameter of the procedure since it is an IDENTITY column.

user_100125190

You can get the stored procedure to return the generated id by providing an output variable to store its content. This behaviour is also documented here.

declare @test int;

EXEC Proposals_Create 'hj','fd','fd','fd','fd','fd','0.23','1.24','fd','fd',
                      '2020/08/30','2020/08/30','2020/08/30','fd','fd', @test OUTPUT;

select @test as 'new_identity';

set @test = null; -- because of the IF statement in your procedure...

EXEC Proposals_Create 'hj','fd','fd','fd','fd','fd','0.23','1.24','fd','fd',
                      '2020/08/30','2020/08/30','2020/08/30','fd','fd', @test OUTPUT;

select @test as 'new_identity';

The selects return 1 and 2 respectively.

Fiddle (Remark: schema name removed in code above and in fiddle).

user_16079510
Related