Nice introduction. I am sure no offence was intended, and non taken by me.

The example was trivial, but let's wait and see what comes next.

When the SELECT statement is compiled for the INSERT, the first value is implicitly taken as an INT. In the UNIONs, as each SELECT is added, they must match this...

Amen.

But I'd like to add GOTO to the list of Mr. Simmons. SQL Server 2000 only...

Oh well, who is John Galt?

AJ and rsharma, thank you both for your answers. I need the UPDLOCK within a transaction, and will act accordingly.

A small question remains: what is the use of using a...

A simplified example:

Before the UPDATE of an Order I have to check the Status of the Order(invoiced or NOT invoiced) and the existance of Order Details. Other users should not be allowed...

Thank you Wanderer, the SQL statement was exactly what I needed!

Ron,

I knew there wouldn't be an easy solution. I just wanted to know how other developers/DBA's think about the Quick and Dirty solution WITH (NOLOCK).

If anything, this thread confirms...

Gentlemen,

Of course we won't forget improving the performance. But as the customers add wishes and need more details, the number of tables in frequently used queries is growing. And growing...

Thank you all for your answers.

I am aware of some of the mentioned issues involved with using WITH (NOLOCK). And I do warn my colleagues not to use this option...

Jon,

Aha, I see a pattern emerging. I will look for ways to extend your script (because I also need to sum on amounts delivered,  amounts invoiced). Couls become quite messy

Jon,

Thank you for your reply. I am afraid I oversimplified.

I need per order the total amount ordered and the total amount assigned. Product has nothing to do with it, so...

I go it wrong, but when I created both tables, inserted the data, I noticed that none of the given answers applied.

A is sorted by myIt.Unique_ID desc

B is sorted...

Gary and Steve,

I should have mentioned that I ran the script in debug mode with AUTOROLLBACK ON. Which, I suppose, is also the answer why @@TRANCOUNT yielded an unlike number....

Roger that

Thanks.

Gerry