Code is skipping over Insert Statement

Question

I'm working on a stored procedure to Add an item to my database. The procedure also handles for editing an item. The code keeps skipping over the insert statement even though in my test run I declare the Id = 0 which should initiate a Post rather than a Put. Can someone point out where in my code I went wrong?

DECLARE @PersonId INT,
    @Description VARCHAR(300),
    @ModifiedById INT,
    @NeedId INT

SET @PersonId = 11
SET @Description = 'Testing again'
SET @ModifiedById = 1


BEGIN
--SET NOCOUNT ON;
--SET XACT_ABORT ON;

DECLARE @TimeStamp DATETIME

SET @TimeStamp = GETUTCDATE()

BEGIN TRY
    BEGIN TRANSACTION

    /*--------------------------------------------------------------------------
    INSERT
    --------------------------------------------------------------------------*/
    IF(@NeedId = 0)
        BEGIN
            INSERT INTO [dbo].[CustomerNeeds]
                       ([PersonId]
                       ,[Description]
                       ,[IsActive]
                       ,[LastUpdated]
                       ,[ModifiedById]
                       ,[CreateDate]
                       ,[CreatedById])
                 VALUES
                       (@PersonId
                       ,@Description
                       ,1
                       ,@TimeStamp
                       ,@ModifiedById
                       ,@TimeStamp
                       ,@ModifiedById)


            SELECT @NeedId = CAST(SCOPE_IDENTITY() AS INT)
        END


    /*--------------------------------------------------------------------------
    UPDATE
    --------------------------------------------------------------------------*/
    ELSE
        BEGIN
            UPDATE 
                [dbo].[CustomerNeeds]
            SET 
                [PersonId] = @PersonId
                ,[Description] = @Description
                ,[IsActive] = 1
                ,[LastUpdated] = @TimeStamp
                ,[ModifiedById] = @ModifiedById
             WHERE 
                Id = @NeedId
        END


    /*--------------------------------------------------------------------------
    DELETE ADDRESS ASSOCIATION TO PERSON
    --------------------------------------------------------------------------*/
    DELETE
    FROM
        [dbo].[CustomerNeeds]
    WHERE
        [PersonId] = @PersonId
        AND
        [Id] = @NeedId



    COMMIT TRANSACTION
END TRY

BEGIN CATCH
    ROLLBACK

    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT
    @ErrorMessage = 'Error happened while saving a customer need -- ' + ERROR_MESSAGE(),
    @ErrorSeverity = ERROR_SEVERITY(),
    @ErrorState = ERROR_STATE();

    -- Raise an error and return
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
    RETURN
END CATCH


-- RESULT SET 1: IList<ICustomerNeedsDataContract>
EXEC    [API].[CustomerNeedsList]
        @PersonId = @PersonId
END

Answer 1

DROP TABLE #CustomerNeeds

CREATE TABLE #CustomerNeeds(
                       [PersonId] INT IDENTITY(1,1) NOT NULL
                       ,[Description] NVARCHAR(100)
                       ,[IsActive] BIT
                       ,[LastUpdated] DATETIME
                       ,[ModifiedById] NVARCHAR(100)
                       ,[CreateDate] DATETIME
                       ,[CreatedById] NVARCHAR(100))                       


INSERT INTO #CustomerNeeds
    (Description, IsActive, LastUpdated, ModifiedById, CreateDate, CreatedById)
VALUES
    ('test row 3', 3, CURRENT_TIMESTAMP, 'test_user', CURRENT_TIMESTAMP, 'test_user')
    ,('test row 4', 4, CURRENT_TIMESTAMP, 'test_user', CURRENT_TIMESTAMP, 'test_user')
    ,('test row 5', 5, CURRENT_TIMESTAMP, 'test_user', CURRENT_TIMESTAMP, 'test_user')

DECLARE @PersonId INT,
    @Description VARCHAR(300),
    @ModifiedById INT,
    @NeedId INT

SET @PersonId = 1
SET @Description = 'Testing again'
SET @ModifiedById = 1

BEGIN
--SET NOCOUNT ON;
--SET XACT_ABORT ON;

DECLARE @TimeStamp DATETIME

SET @TimeStamp = GETUTCDATE()

BEGIN TRY
    BEGIN TRANSACTION

    /*--------------------------------------------------------------------------
    INSERT
    --------------------------------------------------------------------------*/
    IF(@PersonId = 0)
        BEGIN
            PRINT 'Insert'

            INSERT INTO #CustomerNeeds
                       ([Description]
                       ,[IsActive]
                       ,[LastUpdated]
                       ,[ModifiedById]
                       ,[CreateDate]
                       ,[CreatedById])
                 VALUES
                       (@Description
                       ,1
                       ,@TimeStamp
                       ,@ModifiedById
                       ,@TimeStamp
                       ,@ModifiedById)
            SELECT @PersonId = CAST(SCOPE_IDENTITY() AS INT)

            SELECT *
            FROM #CustomerNeeds
        END


    /*--------------------------------------------------------------------------
    UPDATE
    --------------------------------------------------------------------------*/
    ELSE
        BEGIN

            PRINT 'Update'

            UPDATE 
                #CustomerNeeds
            SET 
                [Description] = @Description
                ,[IsActive] = 1
                ,[LastUpdated] = @TimeStamp
                ,[ModifiedById] = @ModifiedById
             WHERE 
                [PersonId] = @PersonId

            SELECT *
            FROM #CustomerNeeds
        END


    /*--------------------------------------------------------------------------
    DELETE ADDRESS ASSOCIATION TO PERSON
    --------------------------------------------------------------------------*/
    DELETE
    FROM
        #CustomerNeeds
    WHERE
        [PersonId] = @PersonId

    COMMIT TRANSACTION
END TRY

BEGIN CATCH
    ROLLBACK

    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT
    @ErrorMessage = 'Error happened while saving a customer need -- ' + ERROR_MESSAGE(),
    @ErrorSeverity = ERROR_SEVERITY(),
    @ErrorState = ERROR_STATE();

    -- Raise an error and return
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)

END CATCH

SELECT *
FROM #CustomerNeeds
END

Answer 2

I wonder if you have the wrong table referenced in your DELETE section?

As things stand, I believe you simply delete the row that has just been inserted.