I have a scenario where I need something similar to
.NET's try-catch-finally block.
CREATE a #temp table
INSERT
#temp
CATCH
RAISERROR
FINALLY
DROP #temp
BEGIN TRY
CREATE TABLE #temp
(
--columns
)
--Process data with other data sets
END TRY
BEGIN CATCH
EXECUTE usp_getErrorMessage
END CATCH
BEGIN FINALLY
DROP TABLE #temp
END FINALLY
Instead of creating a table you could just declare a table variable (which will automatically go away when the query ends).
BEGIN TRY
DECLARE @temp TABLE
(
--columns
)
--do stuff
END TRY
BEGIN CATCH
--do other stuff
END CATCH