error capture in SQL

I have a simple SQL query that reads a table and returns a single value.  The value returned is the name of a stored procedure which is then executed.  If any of these executed SP's the rest within my table of SP names will not run because SQL fails.  Basically I want to ignore when an error occurs.

The SP I have at the moment is:
CREATE PROCEDURE dbo.DailyUpdate
AS
      DECLARE c CURSOR FOR SELECT StoredProcedure FROM PurgeCycleTable ORDER BY RunOrder
      DECLARE @proc sysname
      OPEN c
      FETCH NEXT FROM c INTO @proc
      WHILE @@fetch_status = 0
            BEGIN TRY
                  EXEC (@proc)
            END TRY
            BEGIN CATCH
--                  SELECT
--                        ERROR_NUMBER() as ErrorNumber,
--                        ERROR_MESSAGE() as ErrorMessage;
            END CATCH
            FETCH NEXT FROM c INTO @proc
      CLOSE c
      DEALLOCATE c
RETURN

This returns an error when trying to save it "Incorrect syntax near 'TRY', incorrect syntax near the keyword 'END'

This SP runs perfectly without the TRY CATCH blocks.  Perfectly if considering whatever happens with the executed SP's is not an issue.  But sometimes the executed SP's don't work and I need to not care but still run the remaining SP's.
LVL 10
dij8Asked:
Who is Participating?
 
Aneesh RetnakaranDatabase AdministratorCommented:
Hi dij8,


CREATE PROCEDURE dbo.DailyUpdate
AS
     SET XACT_ABORT OFF
     DECLARE c CURSOR FOR SELECT StoredProcedure FROM PurgeCycleTable ORDER BY RunOrder
     DECLARE @proc sysname
     OPEN c
     FETCH NEXT FROM c INTO @proc
     WHILE @@fetch_status = 0
     BEGIN
          EXEC (@proc)
   
          FETCH NEXT FROM c INTO @proc
     END
     CLOSE c
     DEALLOCATE c
RETURN

/*When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back. When OFF, only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. Compile errors, such as syntax errors, are not affected by SET XACT_ABORT.
*/


Aneesh
0
 
dave4dlCommented:
You cannot ignore the error in sql server 2000 but you can in 2005

see http://www.thescripts.com/forum/thread142533.html
0
 
dij8Author Commented:
I'm done for the day now and Monday is a holiday.  I will test your idea on Tuesday Aneesh.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Aneesh RetnakaranDatabase AdministratorCommented:
Ok.. Even I will be on Vacation till Tuesday
0
 
MrHorizontalCommented:
I presume the table of sproc names is filled *after* checking sysobjects that the sproc actually exists in the database!

Nevertheless, each of those sprocs should have their own error handling in them and returning some value as an OUTPUT parameter. ie if no work can be done it still returns a value that can be interpreted as a success / fail value.

This way you can log all the responses of the sprocs as the cursor runs them, either into a #table or into a proper table for further analysis in the future.

You should avoid using try / catch wherever possible, as processing speed is *drastically* reduced when handling errors!
0
 
dij8Author Commented:
The SET XACT_ABORT seems to help.

how do I check if a SPROC exists when using the whole loop thing?  The below fails because it gets confused with "object_id(N@proc)"
      FETCH NEXT FROM c INTO @proc
      WHILE @@fetch_status = 0
            BEGIN
            if exists (select * from dbo.sysobjects where id = object_id(N@proc) and OBJECTPROPERTY(id, N'IsProcedure') = 1)
                  EXEC (@proc)
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
You need  dynamic query here

exec( 'if exists (select * from dbo.sysobjects where id = object_id('''+@Proc+''') and OBJECTPROPERTY(id, N''IsProcedure'') = 1)  exec '+@Proc )

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.