SQL Server Scripts – How to Run a Bunch of Generated Scripts with Exception Handling?

dynamic-sqlerror handlingsql serversql-server-2016

Before I restore a database, I need to save the contents of a table called dbo.ref_systemSettings if it exists.

So I created a script that calls a procedure that will save the contents of this table in a DBA database, for all databases where this table exists.

That is working fine, but I would like to automate this process, avoiding the copy and paste – see below.

How it works:
When I run the following script I get a bunch of scripts to be executed:

--============================================================================
-- to save the table dbo.ref_systemSettings in all the databases where it exists
-- just run the generated script below at Radhe
--============================================================================
SELECT name, Radhe='exec sp_save ' + '''' 
     + name + '.dbo.ref_systemSettings' + ''''
FROM sys.databases
WHERE OBJECT_ID(name+'.dbo.ref_systemSettings') IS NOT NULL

the query above produce the below:
enter image description here

which I copy and paste, in order to run:

exec sp_save 'my_db_01.dbo.ref_systemSettings'
exec sp_save 'my_db_02.dbo.ref_systemSettings'
exec sp_save 'my_db_03.dbo.ref_systemSettings'
exec sp_save 'my_db_04.dbo.ref_systemSettings'

when I run the whole of the query above, all my tables are saved into the DBA database. This is currently working fine.

Question:

I would like to eliminate the copy and paste.
what could be an effective way to run these scripts, while taking into consideration exception handling, lets say… there is no more space in the DBA database and the table cannot be saved… or any other expection??

Best Answer

The result should be stored in a variable and should execute that SQL. It's called dynamic SQL. You can read more about this here

      BEGIN TRY

       DECLARE @sql varchar(max)
        SELECT @sql = COALESCE(@sql ,'')+'exec sp_save ' + '''' 
             + name + '.dbo.ref_systemSettings' + ''''+';'
        FROM sys.databases
        WHERE OBJECT_ID(name+'.dbo.ref_systemSettings') IS NOT NULL

        EXEC (@sql)

       END TRY
       BEGIN CATCH

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

          SELECT   
             @ErrorMessage = ERROR_MESSAGE(),  
             @ErrorSeverity = ERROR_SEVERITY(),  
             @ErrorState = ERROR_STATE();  

          -- Use RAISERROR inside the CATCH block to return error  
          -- information about the original error that caused  
          -- execution to jump to the CATCH block.  
          RAISERROR (@ErrorMessage, -- Message text.  
                     @ErrorSeverity, -- Severity.  
                     @ErrorState -- State.  
                    ); 

       END CATCH
Related Question