Using a cursor, how to handle the error in SQL

Question

I have multiple databases in which I am looking for a particular column called Countries. If the column exists then I check the space characters in the column. If I loop through a cursor, the DB which is not having a Countries column will throw an error. How can I handle this error?

Concern: the catch block is not handling, please help me how to resolve the issue.

Query as shown below,

CREATE PROCEDURE [dbo].[USP_SMSGeneric_CountrySpace] @DB VARCHAR(100)                                        
As                                        
BEGIN                                        

SET NOCOUNT ON                                    

DECLARE @StudyID  varchar(max)                                    
DECLARE @Databasename VARCHAR(max)                                    
DECLARE @QUERY NVARCHAR(MAX)                                  
DECLARE @Protocol varchar(max)                                    
DECLARE @Servername varchar(max)                                     
DECLARE @script VARCHAR(Max)        
DECLARE @script1 VARCHAR(Max)                                    
DECLARE @initscript NVARCHAR(Max)       
DECLARE @Countries VARCHAR(Max)         
DECLARE @Countryrelease VARCHAR(Max) 

IF OBJECT_ID('TEMPDB..#OBJMISSING') IS NOT NULL DROP TABLE  #OBJMISSING       


CREATE TABLE #OBJMISSING (ERRID INT IDENTITY(1,1),ERRNUM BIGINT,ERRMSG VARCHAR(MAX),DBNAME VARCHAR(MAX))                                        


SET @initscript='
    DECLARE csrStudy CURSOR FOR                                    
    SELECT  ProtocolName, DBName, studyid,DBServer AS Servername from SMSAPP.dbo.studymaster  WITH (NOLOCK)                                   
      WHERE ClientName LIKE ''%NOVARTIS%'' AND studystatus IN (1,2) AND DBServer  IN (''SQL002'' ,''SQL004'',''SQL005'')                                         
    '
EXEC sp_executesql @initscript                                 

OPEN csrStudy                                  
FETCH NEXT FROM csrStudy INTO @Protocol,@Databasename,@StudyID,@ServerName                                    

WHILE @@FETCH_STATUS = 0                                      
BEGIN                                      
    SET @DB = @Servername+'.'+@Databasename        

    SET @script = '

    DECLARE @StrValue VARCHAR(max)

    BEGIN TRY     
        IF EXISTS (         
            SELECT DISTINCT 1 FROM  '+@DB+'.sys.columns c JOIN '+@DB+'.sys.Tables t ON c.Object_ID=t.Object_ID
            WHERE c.Name = ''Countries’'' AND t.name =''tblMaterials'')      
        BEGIN
            SELECT  @StrValue = ISNULL(@StrValue + '','', '''') + Countries’ FROM (
            SELECT DISTINCT (LEN(Countries’ + '','') - LEN(REPLACE(Countries’, '' '', '''') + '',''))CNT,Countries  
            FROM  '+@DB+'.dbo.tblMaterials WITH (NOLOCK) )A WHERE CNT>0
        END
    END TRY

    BEGIN CATCH          
        INSERT INTO #OBJMISSING VALUES 
        (ERROR_NUMBER(),ERROR_MESSAGE(),''+@Databasename+'')     
    END CATCH     

    IF @StrValue IS NOT NULL   -- If any Duplicate values found, then raise an alert           
    BEGIN           
        SELECT '+@StudyID+' As StudyID,
        ''Countries field value Should not have space''   AS Actual ,          
        ''Countries field value exists with space for String :'' + @StrValue AS Discrepancy INTO #tempOutput       

I'm getting the following error:

The OLE DB provider "SQLNCLI10" for linked server "SQL001" does not contain the table ""RAW."dbo"."tblMaterials"". The table either does not exist or the current user does not have permissions on that table

Answer 1

You need a TRY CATCH outside the dynamic SQL.

The error message that is displayed is at parse time, before it even executes (for that EXEC statement). At this moment the engine validates that the tables and objects exist and if not then an error is returned. The execution never starts so it will never get to the CATCH section. This is why the TRY CATCH needs to be outside the dynamic SQL, because the whole dynamic SQL will get rejected after parsing.

---

The error message you are getting is coming from a query like the following:

EXEC('SELECT * FROM [SomeLinkedServer].DatabaseName.SchemaName.NonExistingTable')

Msg 7314, Level 16, State 1, Line 1 The OLE DB provider "SQLNCLI11" for linked server "SomeLinkedServer" does not contain the table ""DatabaseName"."SchemaName"."NonExistingTable"". The table either does not exist or the current user does not have permissions on that table.

If you can wrap this on a TRY CATCH, then the control flow will jump to the catch since the severity of the error is high enough:

BEGIN TRY

    EXEC('SELECT * FROM [SomeLinkedServer].DatabaseName.SchemaName.NonExistingTable')

END TRY

BEGIN CATCH

    SELECT 'This is the catch section'

END CATCH

Please note the difference against this following example, without dynamic SQL:

BEGIN TRY

    SELECT 1 FROM [SomeLinkedServer].DatabaseName.SchemaName.NonExistingTable

END TRY

BEGIN CATCH

    SELECT 1

END CATCH

Msg 208, Level 16, State 1, Line 3 Invalid object name 'DatabaseName.SchemaName.NonExistingTable'.

This is because the whole batch is being rejected after parsing, so it can't jump to a CATCH as it never started execution. When you use dynamic SQL, the parse, compile and execute of the dynamic portion happens at the EXEC point (and that is exactly why it's dynamic), delaying the error throw so it can be caught.

---

I can't supply the full fixed code because what you posted isn't complete. But you should be able to ignore errors if you follow this guideline:

DECLARE @Variable ...

DECLARE MyCursor CURSOR FOR ...

FETCH NEXT FROM MyCursor INTO @Variable

WHILE @@FETCH_STATUS = 0
BEGIN

    BEGIN TRY

        DECLARE @DynamicSQL VARCHAR(MAX) = ... -- The DynamicSQL may have another TRY CATCH inside

        EXEC(@DynamicSQL)

    END TRY

    BEGIN CATCH

        -- Do your catch operation here, you can leave this section empty if you want (not recommended)

    END CATCH

    FETCH NEXT FROM MyCursor INTO @Variable

END