Click here to Skip to main content
15,887,318 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
use test1
go

DECLARE @sdb_name sysname
DECLARE @latest_cycle_all date
DECLARE @latest_cycle_tpa date

DECLARE dbcursor cursor FOR 
SELECT 
name as sdb_name
FROM master.sys.systemdatabases


---- 70 names(Client names) will store in dbcurosr


DECLARE @Sqlstirng nvarchar(4000)
DECLARE @paramlist nvarchar(4000)
SET @paramlist=N'@latest_cycle_out date output'


BEGIN TRY
open dbcursor
FETCH NEXT FROM dbcursor into @sdb_name
WHILE @@FETCH_STATUS=0
BEGIN

SET @SQLSTRING=N'select @latest_cycle_out=DATEADD(M,DATEDIFF(M,0,CONVERT(DATE,SVALUE,101)),0) FROM'+@SDB_NAME+'.dbo.scs_admin'
    where skey='LAST_DAY_PROCESS_CYCLE'

----@SQLSTRING=2016-03-01----

	EXECUTE SP_EXECUTESQL 
	        @SQLSTRING,
			@paramlist,
			@latest_cycle_out=@latest_cycle_tpa output;
			
			IF @latest_cycle_all is null or
			@latest_cycle_all<@latest_cycle_tpa
			SET @latest_cycle_all=@latest_cycle_tpa
			--BREAK
			FETCH NEXT FROM dbcursor into @sdb_name
			END
CLOSE dbcursor 
deallocate dbcursor

select top 48 IDENTITY(int,0,-1) as Nummonths into #monthlist FROM sysobjects,sys.all_columns
select DATEADD(mm,nummonths,@latest_cycle_all) 'Cycle_month',
      CONVERT(varchar(50),dateadd(mm,nummonths,@latest_cycle_all),120),name as [Cyle_month] from #monthlist 
DROP table #monthlist

BEGIN CATCH

--Error Information will be displays from here.

END CATCH

What I have tried:

This Cursor will loop the all 70 names(TPS/Client names) and gives top 48 records for each TPA.
But in this if any TPA(Cients) gets errors with some restore process of their DB its giving error.

Note: I don't want to stop this code if any error comes in middle of the execution with any reason.Its need to be skip that error and process the next TPA(Client) and fetch the data for the remaining TPA's .

I need proper Error handling for this code.


Could you please any one suggest me how to handle this error with proper code.

i placed BREAK  its skipping the error and not fetching the data for all the TPA's,its fetching data for only one TPA.
Posted
Comments
Rajesh Pandya 12-Mar-18 4:36am    
Add try catch in while loop code so if an error comes try-catch block will handle the error and loop will not break and it will continue to process next record
RedDk 13-Mar-18 15:10pm    
Here's how this is going to go, I ask you some questions about the above code and you answer them. But first some corrections in spelling. Obvious errors like that first DECLARE variable ... fix them, and we can start.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900