exception handling: how would you do it?

Question

exception handling: how would you do it?

RightOnTarget

Hall of Fame

Points: 3041
More actions
October 2, 2013 at 3:44 pm

#395594

Hi all,
I have a 3-part UNION ALL query. Sometimes, one of the tables are not available, meaning I can't connect to it.
Currently, if one part of the query fails, the whole thing fails. How do I trap all possible exceptions, so even if any 1 or 2 parts fail, the rest will return a result.
Here is the test data:
create table A (col_1 varchar(1), col_2 int);
insert into A values('a',100);
create table B (col_1 varchar(1), col_2 int);
insert into B values('b',200);
create table C (col_1 varchar(1), col_2 int);
insert into C values('c',300);
SELECT
a.col_1AS td,
a.col_2 AS
FROM
(
select col_1, col_2 from A
union all
select col_1, col_2 from B
union all
select col_1, col_2 from C
) a
FOR XML RAW('tr'), elements
How would you do it?
Thanks,
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

pietlinden SSC Guru Points: 63500 More actions · Answer 1

only thing i can think of is to do the iffy one inside a TRY/CATCH block and trap for the table not being available (if you can). Then use an IF statement to execute the proper SQL statement.

Sean Pearce SSCoach Points: 15999 More actions · Answer 2

create table A (col_1 varchar(1), col_2 int);

insert into A values('a',100);

create table B (col_1 varchar(1), col_2 int);

insert into B values('b',200);

create table C (col_1 varchar(1), col_2 int);

insert into C values('c',300);

-- Use a temp table to store results

CREATE TABLE #Results (col_1 varchar(1), col_2 int);

-- Use try catch blocks for each source table and dynamic sql

BEGIN TRY

INSERT INTO #Results

EXEC sp_executesql N'SELECT col_1, col_2 FROM A;';

END TRY

BEGIN CATCH

PRINT 'Table A Does Not Exist';

END CATCH;

BEGIN TRY

INSERT INTO #Results

EXEC sp_executesql N'SELECT col_1, col_2 FROM B;';

END TRY

BEGIN CATCH

PRINT 'Table B Does Not Exist';

END CATCH;

BEGIN TRY

INSERT INTO #Results

EXEC sp_executesql N'SELECT col_1, col_2 FROM C;';

END TRY

BEGIN CATCH

PRINT 'Table C Does Not Exist';

END CATCH;

-- Return results

SELECT

col_1 AS td,

col_2 AS

FROM

#Results

FOR XML RAW('tr'), ELEMENTS;

DROP TABLE #Results;

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

RightOnTarget Hall of Fame Points: 3041 More actions · Answer 3

Thank you, that looks like something I can try doing.

One question: why do I have to use dynamic sql? Why can't I just:

-- Use a temp table to store results

CREATE TABLE #Results (col_1 varchar(1), col_2 int);

-- Use try catch blocks for each source table and dynamic sql

BEGIN TRY

INSERT INTO #Results

SELECT col_1, col_2 FROM A;

END TRY

BEGIN CATCH

PRINT 'Table A Does Not Exist';

END CATCH;

BEGIN TRY

INSERT INTO #Results

SELECT col_1, col_2 FROM B;

END TRY

BEGIN CATCH

PRINT 'Table B Does Not Exist';

END CATCH;

BEGIN TRY

INSERT INTO #Results

SELECT col_1, col_2 FROM C;

END TRY

BEGIN CATCH

PRINT 'Table C Does Not Exist';

END CATCH;

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 4

Sorry, but I do not see any example using Dynamic SQL. I see the examples using straight Transact SQL.

I NEVER, EVER use Dynamic SQL. No matter what.

Andrew SQLDBA

RightOnTarget Hall of Fame Points: 3041 More actions · Answer 5

My bad, I was referring to

INSERT INTO #Results

EXEC sp_executesql N'SELECT col_1, col_2 FROM A;';

can I use

INSERT INTO #Results

SELECT col_1, col_2 FROM A;

mister.magoo SSC-Forever Points: 47174 More actions · Answer 6

That is to prevent errors in compilation if a table does not exist.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Dwain Camps SSC Guru Points: 86948 More actions · Answer 7

mister.magoo (10/3/2013)
That is to prevent errors in compilation if a table does not exist.

If the code is in an SP, deferred resolution may save the day.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Sean Pearce SSCoach Points: 15999 More actions · Answer 8

AndrewSQLDBA (10/3/2013)
Sorry, but I do not see any example using Dynamic SQL. I see the examples using straight Transact SQL.
I NEVER, EVER use Dynamic SQL. No matter what.
Andrew SQLDBA

Dynamic SQL has it's place. In this example it's to prevent compilation errors. There are no avenues for injection in this code. I also use dynamic sql for maintenance operations.

Just because a tool is widely misused doesn't mean it shouldn't be used at all.

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 9

Sean Pearce (10/4/2013)
AndrewSQLDBA (10/3/2013)
Sorry, but I do not see any example using Dynamic SQL. I see the examples using straight Transact SQL.
I NEVER, EVER use Dynamic SQL. No matter what.
Andrew SQLDBA
Dynamic SQL has it's place. In this example it's to prevent compilation errors. There are no avenues for injection in this code. I also use dynamic sql for maintenance operations.
Just because a tool is widely misused doesn't mean it shouldn't be used at all.

+10

--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)