I agree: AFAIK, the only restriction on the number of variables is memory space.

Maybe:

ORDER BY LEN( Number ) DESC

It sounds as if you do need two LEFT OUTER JOINs, as Wayne suggested.

Other than that, sorry, I give up. Hopefully s/o else can help you.

Please try running this:

SELECT glbank.check_num

, glbank.ref_num

, glbank.check_amt

, glbank.check_date

, glbank.bank_code

, vendaddr.[name]

, vendor.vend_num

, vendor.vend_remit

FROM((aptrxp_all

INNER JOIN glbank

ON aptrxp_all.check_num=glbank.check_num)

INNER JOIN vendaddr

ON aptrxp_all.vend_num=vendaddr.vend_num)

INNER JOIN vendor

ON (vendaddr.vend_num IS NOT NULL AND...

Yes, that's right.

You could consider the LEFT/RIGHT table as the "always" side of the JOIN, with the other, un-named side being the "optional" side.

So, back to your q 2), breaking...

INNER JOIN vendor

ON (vendaddr.vend_num IS NOT NULL AND vendaddr.vend_num=vendor.vend_num)

OR (vendaddr.vend_num IS NULL AND vendaddr.vend_remit=vendor.vend_remit)

Use CONVERT:

SELECT LEFT(CONVERT(varchar(16), amount, 1), LEN(CONVERT(varchar(16), amount, 1)) -3) AS Amount

FROM (

SELECT CAST(1234567.8901 AS money) AS amount

UNION ALL

SELECT...

Of course if the duplicates are both in the same batch you are inserting -- which sounds extremely likely in the scenario you are presenting -- that code won't solve...

Quite. I...

You might add one further refinement to Lutz's excellent query, depending on whether Results column could ever have other values:

SELECT

COUNT(*)...

You could use a trigger on a custom table built to allow it to fire off other procs with params.

That's not especially easy or clean, but it will work.

It's difficult...

Does your book tell you what version of SQL Server? If it's SQL 2000, the answer will be different.

Very nice! You can shorten it up a bit though 🙂

DECLARE @minutesSinceSQLStarted int

SELECT @minutesSinceSQLStarted = DATEDIFF(MINUTE,

-- determines when tempdb was created (done at startup) ...

Maybe the 'GBP' row(s) are being excluded by another condition ... this does start with "AND" 🙂

From a performance standpoint, are you better off putting the calcs on GETDATE() directly in the SQL rather than using variables? I would hope that SQL's optimizer would realize...