September 16, 2010 at 7:41 pm
Hello I have a stored procedure that selects from a bunch of tables. The database name and where clause are constructed from input parameters. I currently have a try catch block to catch any errors the problem is when an error occurrs the procedure stops, I want it to continue and complete the rest of the select statements. Here is the proc
create proc [dbo].[sp_prpc_remove_old_Data_Test] @dbname varchar(100), @days int
as
BEGIN TRY
exec('select count(*) from ' + @dbname + '..pc_history_work_retail
where pxTimeCreated < (current_timestamp - ' + @days + ')')
exec('select count(*) from ' + @dbname + '..pc_history_work
where pxTimeCreated < (current_timestamp - ' + @days + ')')
exec('select count(*) from ' + @dbname + '..pc_work
where pxCreateDateTime < (current_timestamp - ' + @days + ')')
exec('select count(*) from ' + @dbname + '..pca_work_commliab
where pxCreateDateTime < (current_timestamp - ' + @days + ')')
exec('select count(*) from ' + @dbname + '..pca_work_commmotor
where pxCreateDateTime < (current_timestamp - ' + @days + ')')
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000),
@ErrorSeverity INT,
@ErrorState INT
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
select @ErrorMessage,@ErrorSeverity,@ErrorState
--RAISERROR(@ErrorMessage,@ErrorSeverity,@ErrorState );
END CATCH
September 17, 2010 at 6:59 am
You have to try ... catch each block separately:begin try
print 1
end try
begin catch
print ERROR_MESSAGE()
end catch
begin try
raiserror('fake error',16,1)
end try
begin catch
print ERROR_MESSAGE()
end catch
begin try
print 2
end try
begin catch
print ERROR_MESSAGE()
end catch
Output:
1
fake error
2
September 17, 2010 at 11:57 am
What kind of errors are you normally seeing happen with the proc?
You can try something like this -> create another SP that will return the row-count or an error value in case of an error and call that SP from within your SP...that way you can do something if an error happens (if you want to) and continue...
IF OBJECT_ID('dbo.InnerProcedure') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.InnerProcedure;
END
GO
IF OBJECT_ID('dbo.OuterProcedure') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.OuterProcedure;
END
GO
CREATE PROC [dbo].[InnerProcedure] @dbname sysname,@days int, @tablename sysname, @ColName sysname
AS
BEGIN
BEGIN TRY
DECLARE @SqlStr nvarchar(4000), @RowCount int, @RowCountOutput int, @ParamDef nvarchar(100);
SET @SqlStr = 'SELECT @RowCountOutput = COUNT(*) FROM '+@dbname+'.'+@tablename+
' WHERE '+@ColName+' < (current_timestamp - ' + CAST(@days as varchar(30))+ ')';
PRINT '@SqlStr:'+CHAR(13);
PRINT @SqlStr;
SET @ParamDef = '@RowCountOutput int OUTPUT'
EXEC sp_executesql @SqlStr,@ParamDef,@RowCountOutput = @RowCount OUTPUT
RETURN @RowCount
END TRY
BEGIN CATCH
RETURN (-1);
END CATCH
END
GO
CREATE PROC [dbo].[OuterProcedure] @dbname varchar(100), @days int
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ErrMsg nvarchar(2048),@RowCount int;
BEGIN TRY
EXEC @RowCount = dbo.InnerProcedure @dbname,@days,'sys.tables','create_date';
SELECT @RowCount;
-- this call will fail
EXEC @RowCount = dbo.InnerProcedure @dbname,@days,'sys.columns','create_date';
SELECT @RowCount;
EXEC @RowCount = dbo.InnerProcedure @dbname,@days,'sys.tables','create_date';
SELECT @RowCount;
END TRY
BEGIN CATCH
SET @ErrMsg = ERROR_MESSAGE()
SELECT @ErrMsg
END CATCH
END
GO
EXEC dbo.OuterProcedure @dbname = 'master',@days = 100;
The multiple try...catch blocks will fail if you encounter a an error that has a batch termination scope...something like this:
begin try
print 1
end try
begin catch
print ERROR_MESSAGE()
end catch
begin try
-- trying to select a column that doesn't exist
SELECT YYY FROM sys.objects end try
begin catch
print ERROR_MESSAGE()
end catch
begin try
print 2
end try
begin catch
print ERROR_MESSAGE()
end catch
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply