Prev: Return value from stored procedure that returns a recordset
Next: Query to find when a total is hit
From: --CELKO-- on 27 May 2010 19:20
>> The stored procedure is too large to paste but the concept is shown below. Any help is appreciated. <<

The concept IS the problem. You have never had any exposure to the
most basic software engineering. Yourdon? Gane & Sarson? DeMarco?
Constantine? Myers? Etc? This is more than just being a bad **SQL**
programmer.

Look up Coupling and Cohesion. In particular the stuff in Cohesion
about flags. Read why they stink and all the reasons good programmers
never use them as flow controls.

In SQL this is even worse than in procedural code. It implies that you
have split a set into TableA and TableB rather than having those
entities in one table. They probalby ought to be in one table with a
status attribute.

But let's assume that you are actually putting apples and oranges
together or that the UNION ALL makes sense. Based over 30 years in
this trade, that is not how I woudl bet, but here goes

1) Right way: do two queries

SELECT x, y, z
FROM TableA
WHERE branch_something = @in_branch_something

and a second query for the second, totally different set. This is
called cohesion.

SELECT x, y, z
FROM (SELECT x, y, z
FROM TableA
UNION ALL
SELECT x, y, z
FROM TableB)
AS X(x, y, z)
WHERE branch_something = @in_branch_something;

2) A query that mimics your non-relational procedural code attempt.

SELECT x, y, z
FROM TableA
WHERE branch_something = @in_branch_something
UNION ALL
SELECT x, y, z
FROM TableB
WHERE branch_something = @in_branch_something
AND @silly_external_flg = 1;


First  |  Prev  | 
Pages: 1 2
Prev: Return value from stored procedure that returns a recordset
Next: Query to find when a total is hit