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

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

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.]