CODEWITHSUNDEEP
sql-server-2008t-sqlexception
Sinaesthetic
Sinaesthetic

Reputation: 12241

Can I handle an exception and then let it continue on without causing a break in the code?

I have a stored procedure (that I didn't write) that uses openquery to populate a temporary table. The problem is that we have an expected error (it hits active directory for a user that no longer exists) that is stopping the entire procedure. What I was hoping to do is catch the error, fill in some default values and allow the cursor to continue. Currently, I'm catching the error, but the proc is stopping at that point. Is there a way I can force it to continue? Here's the piece of the proc:

   BEGIN
        SET @SQL=N'INSERT INTO #AD_Display_Names (GUID, Display_Name) 
        SELECT objectGUID, displayName 
        FROM OPENQUERY(ADSI,''SELECT objectGUID, displayName 
        FROM ''''LDAP://<GUID=' + CONVERT (VARCHAR (MAX), @GUID) + '>'''' 
        WHERE objectCategory = ''''Person'''' AND objectClass = ''''user'''''')'

        BEGIN TRY
            EXEC SP_EXECUTESQL @SQL
        END TRY
        BEGIN CATCH
            SET @SQL=N'INSERT INTO #AD_Display_Names (GUID, Display_Name) 
            VALUES(''00000000-0000-0000-0000-000000000000'', ''Unknown'')'
            EXEC SP_EXECUTESQL @SQL
        END CATCH

        FETCH NEXT FROM [User_Names_Cursor]
        INTO @GUID
    END

Upvotes: 1

Views: 212

Answers (1)

Chris Gessler
Chris Gessler

Reputation: 23123

Why not do something like this?

-- cursor stuff here

BEGIN
    DECLARE @objectGUID UNIQUEIDENTIFIER
    DECLARE @displayName VARCHAR(100)
    SELECT @objectGUID = objectGUID, @displayName = displayName
    FROM OPENQUERY(ADSI, N'SELECT objectGUID, displayName 
                           FROM ''LDAP://<GUID=' + CONVERT (VARCHAR (MAX), @GUID) + '>''
                           WHERE objectCategory = ''Person'' AND objectClass = ''user'''

    IF(@objectGUID IS NULL)
    BEGIN
        SET @objectGUID = '00000000-0000-0000-0000-000000000000'
        SET @displayName = 'Unknown'
    END

    INSERT INTO #AD_Display_Names (GUID, Display_Name)
    VALUES(@objectGUID, @displayName)

    FETCH NEXT FROM [User_Names_Cursor]                           
    INTO @GUID  
END

Upvotes: 1

Related Questions