error handling using dynamic SQL in cursor

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

error handling using dynamic SQL in cursor

Author

Topic

qutesanju
Posting Yak Master

193 Posts

Posted - 2010-06-16 : 03:40:15

hi all

I created one SP
having input parameter as plan(1111)

I have few table names and their respective columns inserted into one temp table
I have below input temp table
Insert into #MasterTBL values ('ACTIV_PLAN' , 'ACTIV_PLANT_PLAN_CHR')
Insert into #MasterTBL values ('CST_MET' , 'COST_MET_SAP_PLAN_CHR')
Insert into #MasterTBL values ('CST_XREF' , 'CST_XREF_SAP_PLAN_CHR')
Insert into #MasterTBL values ('EQUIPMENT' , 'EQUIPMENT_PLANN_PLAN_CHR')

..........................
..........................
and so on(around 50entries)

now using one cursor i m reading all values of #MasterTBL and forming deleting statement as
using dynamic SQL
SELECT @SQL2 = ' Delete ' +
' FROM dbo.' + quotename(@Table_Name) +
' WHERE' + quotename(@Column_Name) +
= ' + @Plan

but i have to handle error while deleting data also,and i wish to add try catch code for rolling back transcation in case wrong data deletion

but i m not sure where to put below error hadling in above code

begin try
....
....
....
END TRY

BEGIN CATCH
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE()
ROLLBACK TRAN

RAISERROR(@ErrMsg, @ErrSeverity, @ErrorState)
END CATCH

qutesanju
Posting Yak Master

193 Posts

Posted - 2010-06-16 : 10:02:00

experts please advise

qutesanju
Posting Yak Master

193 Posts

Posted - 2010-06-17 : 02:11:52

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources