Altering the stored procedure does not affect the output

Question

In the context of the provided SQL Server stored procedure, I need the SCODE column in myTable to be a value of 0, regardless of the values present in the @TVP_m table.

I have tried:

INSERT INTO [ax].myTable ([AMOUNT], [SUM], [UNIT], [ID],
                          [DATE], [SCODE],   -- The value type is INT
                          [TIME])
    SELECT
        [AMOUNT], [SUM], [UNIT], [ID],
        [DATE], 0,         -- The value type is INT
        [TIME]
    FROM 
        @TVP_m AS Source;

But still no affect, the table value is set by temp table

This is the whole stored procedure:

ALTER PROCEDURE [crt].[INSERTSA]
    @bId   BIGINT,
    @TVP_S [crt].[RETAILSTABLETYPEV2] READONLY
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @i_R     INT;
    DECLARE @i_T     INT;
    DECLARE @i_Error INT;

    SET @i_R = 0;
    SET @i_T = 0;

    IF @@TRANCOUNT = 0
    BEGIN
        BEGIN TRANSACTION;

        SELECT @i_Error = @@ERROR;

        IF @i_Error <> 0
        BEGIN
            SET @i_R = @i_Error;
            GOTO exit_label;
        END;

        SET @i_T = 1;
    END;

    -- Perform Insert
    INSERT INTO [ax].RETAILTNS ([AMOUNT], [SUM], [UNIT], [ID],
                                [DATE], [SCODE], [TIME], [CHANNEL])
        SELECT
            [AMOUNT], [SUM], [UNIT], [ID],
            [DATE], [SCODE], [TIME], @bId
        FROM @TVP_S AS Source;

    SELECT @i_Error = @@ERROR;

    IF @i_Error <> 0
    BEGIN
        SET @i_R = @i_Error;
        GOTO exit_label;
    END;

    IF @i_T = 1
    BEGIN
        COMMIT TRANSACTION;

        SET @i_Error = @@ERROR;

        IF @i_Error <> 0
        BEGIN
            SET @i_R = @i_Error;
            GOTO exit_label;
        END;

        SET @i_T= 0;
    END;

    exit_label:

    IF @i_T = 1
    BEGIN
        ROLLBACK TRANSACTION;
    END;

    RETURN @i_R;
END;

First I need to know why the altered stored procedure does not set the value to 0.

Secondly can someone please provide guidance on how to modify the code to achieve this requirement, ensuring that the CODE column is always initialized with a value of 0?

Answer 1

Modify the INSERT statement within the stored procedure to explicitly set the SCODE value to 0. Then ensure that the rest of the columns are correctly mapped from the @TVP_m table:

USE [RetailStore4567]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [crt].[INSERTDETAILS]
@Id           BIGINT,
@TVP_m          [crt].[myTABLETYPEV2]    READONLY
AS
BEGIN

    SET NOCOUNT ON;

    DECLARE @i_RC       INT;
    DECLARE @i_T        INT;
    DECLARE @i_E        INT;
    
    IF @@TRANCOUNT = 0
    BEGIN
        BEGIN TRANSACTION;

        SELECT @i_E = @@ERROR;
        IF @i_E != 0
        BEGIN
            SET @i_RC = @i_E;
            GOTO exit_label;
        END;

        SET @i_T = 1;
    END;

    -- Performing Insert into myTable

    INSERT INTO [ax].myTable
    (
        [AMOUNT],
        [SUM],
        [UNIT],
        [ID],
        [DATE],
        [SCODE], -- The value type is INT
        [TIME]
    ) 
    SELECT
        [AMOUNT],
        [SUM],
        [UNIT],
        [ID],
        [DATE],
        0, -- Explicitly set SCODE to 0
        [TIME]
    FROM @TVP_m AS Source;

    SELECT @i_E = @@ERROR;
    IF @i_E != 0
    BEGIN
        SET @i_RC = @i_E;
        GOTO exit_label;
    END;

    IF @i_T = 1
    BEGIN
        COMMIT TRANSACTION;

        SELECT @i_E = @@ERROR;
        IF @i_E <> 0
        BEGIN
            SET @i_RC = @i_E;
            GOTO exit_label;
        END;

        SET @i_T = 0;
    END;

exit_label:

    IF @i_T = 1
    BEGIN
        ROLLBACK TRANSACTION;
    END;

    RETURN @i_RC;
END;