Kenneth,

Nice use of modulus but your age calculation is wrong.

eg. A child will come out as 1 year old when he/she is one day old.

Looking back in this thread, my...

sp_execute and sp_executesql are not the same thing.

sp_execute is used with prepared statements by client APIs. (ODBC, ADO.NET etc)

sp_executesql is used by tsql for dynamic queries.

You will need to use a transaction and have an UNIQUE CONSTRAINT/INDEX on Number. Something like:

SET XACT_ABORT ON -- or add your own error handling

DECLARE @NextNumber int

BEGIN TRANSACTION

-- This will...

As B, C and D are joining to A, you should get the same results from all three queries.

The optimizer may behave differently with different join orders. You need to...

Gabola71,

Have you managed to get the execution plans for the query before and after COALESCE was applied?

If you are short on time, it would be good just to compare the...

1. Yes:

'X' + NULL = NULL

while

'X' + ISNULL(NULL, '') = 'X'

or

'X' + COALESCE(NULL, '') = 'X'

etc

2. OK

3. Ouch. Bad design on top of bad design!

1. As you LEFT JOIN to t2, you need an ISNULL around any mention of t2.criteriadata.

2. Can linenum ever exceed 1?

3. Can key/value pairs ever cross a linenum boundary?

I would be inclined to:

1. add OPTION (MAXDOP 1) to Farrell's query, as it may be safer.

2. create the temp table/variable first, to reduce schema locking.

Also, why not use the...

You could try re-directing the output to the nul console by adding > nul:

select @cmd ='C:\"Program Files (x86)"\"Microsoft SQL Server"\90\DTS\Binn\DTExec.exe /DTS "'