How to get stored procedure with transaction to execute if else code block?

Question

I have stored procedure that takes multiple parameters. Based on parameters values either Insert or Edit process will be executed. In each process there are multiple Update/Insert statements (more than one table is updated or inserted). Here is example that I have:

CREATE PROCEDURE dbo.stpAddStatus (
    @record_id  NUMERIC(8),
    @new_type_id NUMERIC(2),
    @current_type_id NUMERIC(2),
    @renew SMALLINT,
    @start_date DATETIME,
    @end_date DATETIME
)
AS
BEGIN
    DECLARE @new_end_dt DATETIME
    DECLARE @approve_end_date DATETIME

    IF @current_type_id != 2 AND @current_type_id != 6 
        SET @new_end_dt = @end_date
    ELSE
        SET @new_end_dt = NULL

    SET @approve_end_date = CONVERT(CHAR(10), DATEADD(dd, -1, @start_date), 101)    

    CREATE TABLE #tmpTbl (
        rec_id NUMERIC(8,0) NULL,
        type_id NUMERIC(3,0) NULL,
        active_status NUMERIC(10,0) NULL
    )

    INSERT INTO #tmpTbl (
        rec_id, 
        type_id, 
        status_id
    )
    SELECT 
        a.rec_id, 
        a.type_id, 
        a.active_status 
    FROM profile a 
    WHERE a.related = @record_id  
        AND type_id IN (10,12,13)

    BEGIN TRANSACTION
        IF (@new_type_id = @current_type_id) AND @renew = 0
            SELECT 'New Type ID is the same as Current Type ID and Renew is 0' AS Message
            /* In this block few different tables should be updated. */
        ELSE
            /* In this block record should be inserted in few different tables. */
            SELECT 'New Type ID is not the same as Current Type ID and Renew is 1' AS Message
        IF @@error !=0
        BEGIN 
            SELECT 1 AS Status, 'Error!' AS Message     
            ROLLBACK TRANSACTION
            RETURN 1
        END
    COMMIT TRANSACTION

    DROP TABLE #tmpTbl 
END

Code above will execute code in both If and Else statement. I'm not sure why, even parameters passed in the SP are this:

EXECUTE stpAddStatus 45645, 4, 4, 0, '04/23/2018', '06/22/2019'

I'm not sure if my if/else block statement is correct or there is something else that I missed. Please let me know if you see why the code is failing and executing both statements no matter what parameters are passe in SP.

Answer 1

If you plan to execute multiple commands for the if and/or else block, you must wrap that set of commands in a BEGIN/END pair, eg:

if .... 
begin
    ... 'if' command #1
    ... 'if' command #2
    ...
    ... 'if' command #n
end
else
begin
    ... 'then' command #1
    ... 'then' command #2
    ...
    ... 'then' command #n
end

The BEGIN/END are optional if there is only a single command in the if and/or else block; when in doubt, include the BEGIN/END wrapper.

Also, the @@error variable is (re)set after each command; I'm guessing you want to rollback your transaction if any of the commands error out, which means you'll need to add some more logic to test @@error after each command; whether you abort after the first error, or at the end of the if/then block is your call.

One other item, if your proc is called within a higher level transaction (eg, you're running in chained mode), then your rollback transaction will actually rollback all transactions. [Nested transaction management is a whole 'nother topic.]