0

I have multiple SQL Scripts like the one below, and in each script I'm inserting data into a table or creating tables (separate scripts), all covered by error handling and logging the error or success messages.

The script below inserts data into a single table, from a source database. I have similar scripts that contain DDL (CREATE TABLE) statements.

My challenge at this point is that I have been asked to turn this script into a template, where all the error handling and logging parts are turned into a template and the only input to my template is the "SQL INSERT" / "CREATE TABLE".

SET NOCOUNT ON;

DECLARE @current_database_schema_version numeric(8,4)=0;

SELECT
    @current_database_schema_version = attribute_value
FROM SystemDetails
WHERE attribute = 'MigratedSchemaVersion';

IF @current_database_schema_version = 0.1
BEGIN

    DECLARE @master_script_id int;
    INSERT INTO DataMigrationHistory_Master (script_name, upgraded_version_no) VALUES ('dm_K_table.sql', 0.1);
    SELECT @master_script_id = SCOPE_IDENTITY();

    /* @migration_messages table variable is needed because during nested transactions, 
        if there is an error and a rollback occurs, logged messages in a persisted tables are also rolled back */

    DECLARE @migration_messages TABLE (
        master_script_id int
        , logged_message varchar(100)
        , rows_affected int
        , run_time datetime2(4) default GETUTCDATE()
        , error_nr int
        , error_severity_nr int
        , error_state_nr int
        , error_line_nr int
        , error_msg varchar(300));

    DECLARE @rowcount int = 0;

    /* Disable ALL Indexes on tables */
    ALTER INDEX NCU_SyncID          ON [K_table] DISABLE;
    ALTER INDEX NC_LastUpdatedOn    ON [K_table] DISABLE;

    BEGIN TRY
        BEGIN TRANSACTION K_tableDataInsert

            /* ----------------------------------------------------------------- */
            /* Insert K_table data */

            SET @rowcount = 0;

            INSERT INTO [K_table] 
                (P_PRNO
                , P_PBEZ
                , P_GUD
                , P_UND_NR
                , P_RESPONSIBLE
                , P_STATE_ID
                , P_CREATED_ON
                , P_CREATED_BY
                , P_LAST_UPDATE
                , P_LAST_UPDATED_BY)
            SELECT
                P_PRNO
                , P_PBEZ
                , P_GUD
                , P_UND_NR
                , U.US_ID
                , NULL
                , P_E_DAT
                , NULL
                , dateadd(second, cast(SUBSTRING(P_M_TIM, 7, 2) as int),
                    dateadd(minute, cast(SUBSTRING(P_M_TIM, 4, 2) as int), 
                        dateadd(hour, cast(LEFT(P_M_TIM, 2) as int), KP_M_DAT)
                            )
                        )
                , NULL
            FROM [SourceDB].[dbo].K_table K
                LEFT JOIN [USER] U
                    ON K.KP_RESPONSIBLE collate Latin1_General_CI_AS = U.US_USER collate Latin1_General_CI_AS;

            SET @rowcount = @@ROWCOUNT;

    END TRY

    BEGIN CATCH
        IF @@TRANCOUNT > 0
        BEGIN

            INSERT INTO @migration_messages (
                    master_script_id
                    , logged_message
                    , error_nr
                    , error_severity_nr
                    , error_state_nr
                    , error_line_nr
                    , error_msg)
            SELECT
                @master_script_id
                , 'Error while inserting data into K_table table'
                , ERROR_NUMBER()
                , ERROR_SEVERITY()
                , ERROR_STATE()
                , ERROR_LINE()
                , ERROR_MESSAGE();

            ROLLBACK TRANSACTION K_tableDataInsert;

            UPDATE DataMigrationHistory_Master
            SET  logged_message = 'Script did not run successfully'
                , error_msg = 'Error encountered during the data migration. Check DataMigrationHistory_Detail for more information'
            WHERE id = @master_script_id;

            INSERT INTO DataMigrationHistory_Detail (
                master_script_id
                , logged_message
                , rows_affected
                , run_time
                , error_nr
                , error_severity_nr
                , error_state_nr
                , error_line_nr
                , error_msg)
            SELECT 
                master_script_id
                , logged_message
                , rows_affected
                , run_time
                , error_nr
                , error_severity_nr
                , error_state_nr
                , error_line_nr
                , error_msg
            FROM @migration_messages
            ORDER BY run_time ASC;

        END
    END CATCH

    IF @@TRANCOUNT > 0
    BEGIN

        INSERT INTO DataMigrationHistory_Detail (
            master_script_id
            , logged_message
            , rows_affected)
        SELECT
            @master_script_id
            , 'Successfully inserted data into K_table table'
            , @rowcount;

        COMMIT TRANSACTION K_tableDataInsert;
    END

    /* Rebuild indexes */
    ALTER INDEX ALL ON [K_table]
    REBUILD;

END

I have tried looking over putting all the error handling code into a stored procedure, and having a NVARCHAR parameter which would be my SQL query and execute that query with dynamic SQL (exec sp_executesql) inside the template.

That would work for the DML scripts, the problem however, is that I have DDL scripts that have this same template for error handling and logging and I know stored procedures cannot run DDL queries.

Is there any other way I can turn this process into a template in SQL for both DDL and DML?

  • @Radu Gheorghiu >>>I know stored procedures cannot run DDL queries<<< This is wrong, stored procedures CAN run DDL statements – sepupic Oct 15 at 9:08
  • 2
    @George.Palacios >>> ROLLBACK won't affect most DDL operations (Won't drop a table as an example)<<< This is not true too. You can rollback create table and you can rollback drop table – sepupic Oct 15 at 9:10
  • @sepupic well, that was unexpected. I thought stored procedures cannot create tables. was it always possible? I remember working with 2008R2 and I think this didn't work. – Radu Gheorghiu Oct 15 at 9:20
  • 1
    It always worked. Not all DDL can be executed in transaction, this is true, but it has nothing to do with "be executed within stored procedures". And some statements need to be the first in the batch, so we wrap them in dynamic code, but it still can be executed in stored procedure – sepupic Oct 15 at 9:28
  • 1
    @RaduGheorghiu, I remember executing (supported) DDL in a transaction as far back as SQL 2000. – Dan Guzman Oct 15 at 12:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.