Note this is an 8 year old thread. :w00t:

thayes 89705 (2/3/2012)

---

I am guessing this would return:

0

1

2

1

But, maybe the ROLLBACK here would actually force all linked transactions to be rolled back and the last @@TRANCOUNT would return 0? ...

Here is a greatly simplified skeleton of what you described you are running.

create procedure proc1

as begin

begin transaction

select 'Proc 1'

rollback transaction

end

go

create procedure proc2

as begin

begin transaction

select 'Proc 2'

rollback transaction

end

go

begin try

begin transaction

exec Proc1;

exec...

My guess is that either proc1 or proc2 encountered an exception an did a rollback, when returning to the main calling block there would be no transactions.

Take a look at...

Well since this is relatively quick you could just put it inside a transaction. At least that should prevent getting duplicates, unless there are dirty reads.

chris.r.armstrong (2/3/2012)

---

Ah, sorry...I'll have to go check how to extract the table DDL, I'm guessing there's probably one of those BEFORE YOU POST threads I should've read somewhere? 😉

Michael, does...

Well your subquery by itself is not valid.

(select isNull(max(UserSequence), 0)

From where? What is UserSequence?

It is hard to tell exactly what you are trying to do but you probably need...

So you want to delete sales records if they belong to a contact that has a duplicate first name? Wouldn't you want to update the sales record?

I still think you are creating a lot of unneeded work but I of course have no idea about your business requirements. Probably the easiest way to get that in...

Cadavre (2/3/2012)

---

Sean Lange (2/3/2012)

---

Are you trying to build an update sproc that will only update the columns that are supplied? That seems like an awful lot of extra overhead and...

Are you trying to build an update sproc that will only update the columns that are supplied? That seems like an awful lot of extra overhead and processing. Given what...

Given what you need for output that is about all you can do. I just wanted to make sure you understand how much data can be pulled back from a...

ssc_san (2/2/2012)

---

Thank you Sean!

I have tried something like this:

select program, pstatus, form from

(select * ,

ROW_NUMBER() over (partition by program order by len(form)) as RowNum

from #pt

group by program, pstatus, form)...

Dave Brooking (2/2/2012)

---

Sean,

Is the group by clause needed in this case?

I tend to use CTEs (and probably overuse them). Is there any advantage to your approach (I think it...

The join itself it so much an issue on...