lmeinke (11/6/2009)

---

But in looking back at it I'm confused as to how the SQL Server engine could process (reliably) two different tables on the same side of the join statement....

Yeah - I'm naturally anti-red-text too.

Rob Fisk (11/6/2009)

---

...

Generally one would do this with triggers (though you have to be careful to handle UPDATEs and DELETEs on both tables gracefully too), though if you are happy to lose...

Cool. The other option would be dynamic SQL, but personally I like the UNION.

You might like to change the ROLLBACK TRANSACTION to:

IF XACT_STATE() != 0 BEGIN ROLLBACK...

It's...

Nice. Though personally I would have called the database to throw the exception 😀

...compare the plan for the example that does work (with the UNIONs) against the plan for this:

--

-- This also works

--

DECLARE @Choice INTEGER;

SET @Choice = 3;

IF ...

Hey Rob,

Check this out 🙂

--

-- This doesn't work

--

DECLARE @Choice INTEGER;

SET @Choice = 3;

IF @Choice = 1

BEGIN

SELECT TOP (1) a = 1...

Luis,

Just a guess really, but:

SELECT OW.data,

LEFT(OW.docnome,3) + ' nº' + RIGHT('00000' + OW.intid, 5) as 'DocTesouraria',

...

Hmm I'm not sure you're...

An alternative, mostly because I'm a bit bored:

SELECT ColA, ColB, colC

FROM (

SELECT ColA, ColB, colC,

...

Yeah I...

You could also use XML:

CREATE PROCEDURE dbo.SelectMany

@data XML OUTPUT

AS

BEGIN

SELECT

@data =

...

Ok, just for fun, here's a complete implementation (I think!)

Minimal comments: half the fun is figuring out how it works 🙂

This sample allows an empty table, until the first record...