Where in error

Based on the T-SQL snippet you've provided, I can see a few structural issues that would cause an error. The BEGIN TRY is not correctly matched with END TRY, and there's a misplaced END.

Here is a corrected version of your script with explanations for the changes:

DECLARE @myrowid INT;
DECLARE SplitProviderCursor CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
SELECT
    rowid
    , npi
FROM
    #tmp_npi_list;

OPEN SplitProviderCursor;

FETCH NEXT FROM SplitProviderCursor
INTO
    @myrowid
    , @Npi;

WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        TRUNCATE TABLE #tempTable;

        EXECUTE dbo.usp_File_Extract_ProviderMasterFile_VA_SPLIT
            @inp_region_nbr = @inp_region_nbr
            , @inp_npi = @Npi
            , @XMLVARCHAR = @Snapshot OUTPUT
            , @providerid = @ProviderID OUTPUT;

        INSERT INTO #tempTable
            (   entity_id
                , run_id
                , partition_id
                , provider_id
                , sha_value
                , snapshot
                , etl_create_dttm
            )
        VALUES
            (@Npi
            , @inp_run_id
            , @inp_partition_id
            , @ProviderID
            , HASHBYTES('SHA2_512', @Snapshot)
            , @Snapshot
            , CURRENT_TIMESTAMP);

        IF LEN(@Snapshot) < 1067142
        BEGIN -- Added BEGIN for the IF block
            INSERT INTO dbo.PROVIDER_EXTRACT_TEMP_MOD
                (   entity_id
                    , run_id
                    , partition_id
                    , provider_id
                    , sha_value
                    , snapshot
                    , etl_create_dttm
                )
            VALUES
                (@Npi
                , @inp_run_id
                , @inp_partition_id
                , @ProviderID
                , HASHBYTES('SHA2_512', @Snapshot)
                , @Snapshot
                , CURRENT_TIMESTAMP);
        END; -- Added END for the IF block

    END TRY
    BEGIN CATCH
        -- You should handle the error here. For example, you could print the error information.
        -- If you do nothing, the error will be caught but ignored.
        PRINT 'An error occurred for NPI: ' + CAST(@Npi AS VARCHAR(20));
        PRINT ERROR_MESSAGE();
        -- It's good practice to re-throw the error if you can't handle it,
        -- or log it for later investigation.
        -- THROW; 
    END CATCH;

    FETCH NEXT FROM SplitProviderCursor
    INTO
        @myrowid
        , @Npi;
END; -- This closes the WHILE loop

-- Don't forget to close and deallocate the cursor
CLOSE SplitProviderCursor;
DEALLOCATE SplitProviderCursor;

Summary of Errors and Fixes:

1. Mismatched BEGIN TRY Block: Your original script had a BEGIN TRY but it was followed by a standalone END and then an invalid END TRY.
   • Fix: I've properly structured the TRY...CATCH block. Every BEGIN TRY must be followed by an END TRY, which in turn must be immediately followed by a BEGIN CATCH and then an END CATCH. I've added a basic BEGIN CATCH...END CATCH block to handle potential errors.
2. Misplaced END: There was an END statement after your second INSERT statement. In T-SQL, an IF statement only needs a BEGIN...END block if it contains more than one statement. While your IF had only one INSERT, it's good practice to use BEGIN...END for clarity, which I have added. The original END was syntactically incorrect in that position.
3. Cursor Not Closed: The WHILE loop would finish, but the cursor was never closed or deallocated. This can lead to resource leaks in SQL Server.
   • Fix: I added CLOSE SplitProviderCursor; and DEALLOCATE SplitProviderCursor; after the loop finishes.
4. FETCH NEXT outside TRY...CATCH: The FETCH NEXT statement was placed between the main logic and the end of the loop. I've moved it to be the last operation inside the WHILE loop, but outside the TRY...CATCH block, which is a common and correct pattern. This ensures that you always fetch the next row, even if the processing for the current row fails and is caught by the CATCH block.

I hope this helps you resolve the issue in your script! Let me know if you have any other questions.