Well it does recheck the condition for every row... but it doesn't mean that the server doesn't use some sort a left join to do the check internally and fast...

Set nocount is more of a network boost (small but not insignificant). The nolock will speed up the reads but that can include uncomited data in the selects, can...

Your solution is static, the maximum number of stages is 3. For having worked with contract steps, believe me that the list can be very long (seen up to...

It would probabely be a good idea to have dates it that table to know when everything hapenned.

As I said it can be done... but when you have 30 objects doing that dance and the manager says, when need to get 6 stages in there instead of...

well you'd only have a single table containing the caseid, what state it's (probabely would take a stage table too) in, and the stagestatusid. Then it's just a simple 2-3...

Yup this guy is good... Other articles from him that you can't not read :

The Curse and Blessings of Dynamic SQL

Dynamic...

Yes you can join to the same table as many times as you want.

But this is a case where you need to build your table differently :

CaseID

StageID (or StageNumber)

StageStatusID

make...

You have the right syntax. Why do you need to use that?

I was reffering to my situation... I didn't check your relations situation.

Yup, that's why I preffer making a single sp that deletes form 7 tables instead of on delete/cascade. This would put me in a situation where a delete of...

Wouldn't you be a little impatient if you were in my shoes ... and I don't wanna keep him waiting too long.

Did you get it this time? Looks like you're not getting the notifications of PM anymore (or you're just busy working .

I think I heard my name in this thread .

Hehe, I learn something new everyday... espicially today. I didn't know that the server was validating this... but then again I don't use on cascade delete very often.