if exists(

select fk from tbl group by fk having count(distinct id) != (select count(distinct id) from tbl)

)

select @retval = 0

Maybe the production environment is using a suboptimal stored plan. You can get rid of all plans with DBCC FREEPROCCACHE, but that could cause temporary performance problems on the prod...

Nice one, Pam!

I don't think this happens in SQL9, as individual statements have their own plans. I stand to be corrected of course.

Yes, it is unclear. The column needs to be a column in the outer table (i.e. the one to the right of the left join) which cannot be null. You...

Better post the execution plans then.

Is your variable the right datatype?

It may be that SQL server has a suboptimal plan based on an atypical value for this param. Try DBCC FREEPROCCACHE and then run the procedure twice with a typical...

Here's previous post on the same topic:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=289159

you should use a left join and check for NULL in a join column of the outer table. The alternaitive is concatenation but...

To expand on the above, you need to create parameter objects and add them to the parameters collection of the command object, which should be of 'stored procedure' type.

Yes, wasn't sure and didn't look it up. Largely irrelevant to the issue though.

I wouldn't combine data cleansing with ordinary DML. Check the values using a SELECT with ISDATE() first, then fix any corrupt values, then move the data.

If you have mixed date formats,...

Yes, I was wondering about that. At the point in time at which you insert a new record, there oughtn't to be any records referencing it - what did they reference...

Ask this joker what he means by product-oriented. It sounds as though he means 'trying to use rows as columns'.

You need to give us a little more background. Your current...

Since your issue is maintenance/code reuse, a view would be the best solution, rather than a derived table (inline view).

It's not particularly useful here since the calculations are simple...