Ignoring error and continue
-
Hi,
I am writing a Stored Procedure to update a table in multiple databases. In some on the databases that table doesnot exist. So, I want to ignore the error and continue to next database update.
I also want to stop showing error at all while executing the Stored Procedure.
So, please help to ignore and disable the showing of errors.
Thanks in advance.
Afreen
-
Rather than ignoring the error, it would be better to avoid it by first checking for the table's existence:
IF OBJECT_ID('dbo.Test', 'U') IS NOT NULL
BEGIN
-- Add code here
END;The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Hi Phir,
Thanks for the response. I will try your suggestion.
But, also want to check if there is a way to ignore the errors and stop showing error message?
I have to implement more functionality where I guess I will need to ignore the errors.
Thanks.
-
I don't approve of doing this – it wouldn't get past me in a code review – but you can do something like this:
BEGIN TRY
SELECT 1 / 0;
END TRY
BEGIN CATCH
END CATCH;
SELECT 'Error got swallowed'The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Hi Phil,
Thanks. But, it is still showing errors.
-
Hi Phil,
Below is my code: @db_name is coming from the cursor which is fetching the db_name from master database.
SET @qry = 'SET NOCOUNT ON Declare @days INT BEGIN TRY select @days=LongValue from ' + @db_name + '.dbo.Settings
where ID = ''100''
PRINT @days
update ' + @db_name + '.dbo.employee
set isactive = 0
where abs(datediff(day, GETDATE(),LoginDate)) > @days
PRINT @@ROWCOUNT
END TRY
BEGIN CATCH
END CATCH;'
EXEC(@qry)
It is executing update statement fine for the dbs where employee table is present, but where the employee table is not there it is giving error.
I want ignore the error and dont want to show them at all.
Please help me on this.
- Phil Parkin wrote:
I don't approve of doing this – it wouldn't get past me in a code review – but you can do something like this:
BEGIN TRY
SELECT 1 / 0;
END TRY
BEGIN CATCH
END CATCH;
SELECT 'Error got swallowed'I think if a query referenced a non existing table, errors work differently, this snippet didn't print or select anything! Sort of like a "compile time" error.
BEGIN TRY
select bugs from bogus_table_name
END TRY
BEGIN CATCH
print 'caught error'
END CATCH;
SELECT 'Error got swallowed'edit: this is like the worst forum software ever!
-
How about this?
SET @qry
= 'SET NOCOUNT ON Declare @days INT select @days=LongValue from ' + @db_name
+ '.dbo.Settings
where ID = ''100''
PRINT @days
update ' + @db_name
+ '.dbo.employee
set isactive = 0
where abs(datediff(day, GETDATE(),LoginDate)) > @days
PRINT @@ROWCOUNT
';
BEGIN TRY
EXEC (@qry);
END TRY
BEGIN CATCH
END CATCH;The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
The problem with this is, it comes out of the cursor and do not continue Fetch statement.
-
And the problem with your question is that you did not give me the full picture.
Now you are drip-feeding clues into the thread as they come up, which is not my preferred way of working. Maybe someone else, who has more patience than I, will assist.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin - Phil Parkin wrote:
And the problem with your question is that you did not give me the full picture.
Now you are drip-feeding clues into the thread as they come up, which is not my preferred way of working. Maybe someone else, who has more patience than I, will assist.
Nah... it was just easy to miss. See this post of the op.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
To get SQL Server to ignore and continue is possible from a script or procedure by executing another (or more...) stored procedure which contain try/catch where the error is output as a variable. Within the scope of a single statement "ignoring an exception" is nonsensical. From PROC_A run PROC_B and output result from PROC_B TRY/CATCH (depending on what happened).
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
Hi Phil,
I am sorry if my question/code created a confusion. I am putting the entire code below. Could you please look at that:
***********************************************************************
DECLARE @db_name VARCHAR(100), @qry VARCHAR(500)
Declare @msg varchar(100)
DECLARE db_cursor CURSOR FOR
SELECT Name FROM Alldatabases
ORDER BY Name
OPEN wh_cursor
FETCH NEXT FROM db_cursor
INTO @db_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @db_name = @db_name
SET @qry
= 'SET NOCOUNT ON Declare @days INT select @days=LongValue from ' + @db_name + '.dbo.Settings
where ID = ''100''
PRINT @days
update ' + @db_name + '.dbo.employee
set isactive = 0
where abs(datediff(day, GETDATE(),LoginDate)) > @days
PRINT @@ROWCOUNT'
EXEC(@qry)
SET @msg = convert(varchar(100), @@ROWCOUNT) + ' rows updated.'
FETCH NEXT FROM db_cursor
INTO @db_name
END
CLOSE db_cursor
DEALLOCATE db_cursor?***************************************************************
I appreciate your time and sorry again.
-
You cannot get around this. When your batch is compiled, the UPDATE statement checks for object existence. This will cause an error. Not even in a TRY..CATCH
The only way to do this is perform the check outside the UPDATE statement string concatenation.
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'employee')
BEGIN
SET @qry = 'SET NOCOUNT ON Declare @days INT
BEGIN TRY
update ' + @db_name + '.dbo.employee
set isactive = 0
where employeeid = 1
end try
begin catch
select ''no table in ' + @db_name + ' ''
end catch
PRINT @@ROWCOUNT
'
EXEC(@qry)
END -
There's a lot going on in the code which could be looked at. Instead of EXEC(@qry) where the parameters are strung together it could be parameterized using sp_executesql. In terms of ignore/continue if the object does not exist what Steve says above is exactly right. Although stored procedures are compiled with late binding to objects, meaning you can create a procedure which references a table that doesn't exist. But when the procedure is run if the table doesn't exist nothing will catch the error.
Suppose there are: 1 table (dbo.test_t) and 2 procedures (dbo.test_proc_a and dbo.test_proc_b).
drop table if exists dbo.test_t;
go
create table dbo.test_t(x int not null);
godrop proc if exists dbo.test_proc_a;
go
create proc dbo.test_proc_a
as
set nocount on;
set xact_abort on;
begin transaction
begin try
declare
@output_bit bit,
@output_msg nvarchar(max);
exec dbo.test_proc_b N'dbo.test_t', @result_bit=@output_bit output, @result_msg=@output_msg output;
print (cast(@output_bit as nchar(1)));
print (cast(@output_msg as nvarchar(max)));
print ('xact_state='+cast(xact_state() as nvarchar(3)));
if (xact_state()=-1)
begin
rollback transaction;
begin transaction;
end
exec dbo.test_proc_b N'pdq', @result_bit=@output_bit output, @result_msg=@output_msg output;
print (cast(@output_bit as nchar(1)));
print (cast(@output_msg as nvarchar(max)));
print ('xact_state='+cast(xact_state() as nvarchar(3)));
if (xact_state()=-1)
begin
rollback transaction;
begin transaction;
end
exec dbo.test_proc_b N'dbo.test_t', @result_bit=@output_bit output, @result_msg=@output_msg output;
print (cast(@output_bit as nchar(1)));
print (cast(@output_msg as nvarchar(max)));
print ('xact_state='+cast(xact_state() as nvarchar(3)));
if (xact_state()=-1)
begin
rollback transaction;
end
if (@@trancount>0)
commit transaction;
end try
begin catch
print (error_message());
rollback transaction;
end catch
godrop proc if exists dbo.test_proc_b;
go
create proc dbo.test_proc_b
@sys_table_name nvarchar(256),
@result_bit bit output,
@result_msg nvarchar(max) output
as
set nocount on;
begin try
/* test to make sure the table exists */
--if (object_id(@sys_table_name, 'U') is null)
-- throw 50000, 'The table does not exist', 1;
if (object_id('dbo.test_t', 'U') is null)
throw 50000, 'The table does not exist', 1;
/* 1) do something that works */
insert dbo.test_t(x) values(1);
/* 2) create an exception on purpose divide by zero and return system error message*/
--insert dbo.test_t(x) values(4/0);
/* 3) create an exception on purpose using THROW */
--declare @error_msg nvarchar(max)=concat(N'test_proc_b input @sys_table_name=', @sys_table_name);
--throw 50000, @error_msg, 1;
select @result_bit=cast(1 as bit);
select @result_msg=N'Ok';
end try
begin catch
select @result_bit=cast(0 as bit);
select @result_msg=error_message();
end catch
gotest_proc_a executes test_proc_b three times passing in different table names and outputting variables to ignore/continue if dbo.test_proc_b was successful or not.
Test 1) Suppose the three objects above are created: drop table dbo.test_t and execute dbo.test_proc_a
drop table if exists dbo.test_t;
go
exec dbo.test_proc_a;Results
0
The table does not exist
0
The table does not exist
0
The table does not existTest 2) Suppose the three objects above are created (don't drop table)
exec dbo.test_proc_a;
Results
1
Ok
1
Ok
1
OkTest 3) Suppose the three objects above are created. Uncomment #2 in dbo.test_proc_b and comment out #1
drop proc if exists dbo.test_proc_b;
go
create proc dbo.test_proc_b
@sys_table_name nvarchar(256),
@result_bit bit output,
@result_msg nvarchar(max) output
as
set nocount on;
begin try
/* test to make sure the table exists */
--if (object_id(@sys_table_name, 'U') is null)
-- throw 50000, 'The table does not exist', 1;
if (object_id('dbo.test_t', 'U') is null)
throw 50000, 'The table does not exist', 1;
/* 1) do something that works */
--insert dbo.test_t(x) values(1);
/* 2) create an exception on purpose divide by zero and return system error message*/
insert dbo.test_t(x) values(4/0);
/* 3) create an exception on purpose using THROW */
--declare @error_msg nvarchar(max)=concat(N'test_proc_b input @sys_table_name=', @sys_table_name);
--throw 50000, @error_msg, 1;
select @result_bit=cast(1 as bit);
select @result_msg=N'Ok';
end try
begin catch
select @result_bit=cast(0 as bit);
select @result_msg=error_message();
end catch
go
exec dbo.test_proc_a;Results
0
Divide by zero error encountered.
0
Divide by zero error encountered.
0
Divide by zero error encountered.Test 4) Suppose the three objects above are created. Uncomment #3 in dbo.test_proc_b and comment out #2
drop proc if exists dbo.test_proc_b;
go
create proc dbo.test_proc_b
@sys_table_name nvarchar(256),
@result_bit bit output,
@result_msg nvarchar(max) output
as
set nocount on;
begin try
/* test to make sure the table exists */
--if (object_id(@sys_table_name, 'U') is null)
-- throw 50000, 'The table does not exist', 1;
if (object_id('dbo.test_t', 'U') is null)
throw 50000, 'The table does not exist', 1;
/* 1) do something that works */
--insert dbo.test_t(x) values(1);
/* 2) create an exception on purpose divide by zero and return system error message*/
--insert dbo.test_t(x) values(4/0);
/* 3) create an exception on purpose using THROW */
declare @error_msg nvarchar(max)=concat(N'test_proc_b input @sys_table_name=', @sys_table_name);
throw 50000, @error_msg, 1;
select @result_bit=cast(1 as bit);
select @result_msg=N'Ok';
end try
begin catch
select @result_bit=cast(0 as bit);
select @result_msg=error_message();
end catch
go
exec dbo.test_proc_a;Results
0
test_proc_b input @sys_table_name=dbo.test_t
0
test_proc_b input @sys_table_name=pdq
0
test_proc_b input @sys_table_name=dbo.test_tAus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply