[RESOLVED] UNION isn't returning what I thought it would (SQL Server)

[FunkyDexter]

I know I'm coming to this a bit late but I'd have achieved the original goal with a left join and case:-

Select Active.ID,
Active.Name,
Case When Pending.ID is null then 'Active' Else 'Pending' END
From Active
Left Join Pending
on Active.ID = Pending.ID

Not sure it's any better or worse than the other suggestions but it is an alternative.

As for the transaction discussion, if your SSMS is holding transactions open at the end of the day it's probably not to do with the transactions you're putting in your sprocs but more likely to be from ad-hoc queries you've run during the day where you've opened a transaction and then forgotton to close it. Or as Sz put it:-

When you close windows and it tells you that you had "open transactions" - well that means you ignored an ERROR of some sort in an earlier test of the query - and it never reached the COMMIT.

Whether you need a transaction in the scanario you describe really depends on the business. If for example, a provider become active while your query is running and you miss-report them as pending, does it matter? If it does then you need a transaction.


edit> And you can never trust squirrels. Although in your example it would have bought the whole server down so your open transactions are unlikely to be the biggest problem you'll have to deal with.

We used to have a rule that you were allowed to use transactions on the server (as long as they were properly managed) but never to open one from the client. The reasoning was that a server crash means you don't care about a left ofer transaction but a client crash could leave that resource open on the server. I'm not sure that's true but it was an easy enough rule to follow so I never felt the need to find out.