Have you tried decimal(18,2) instead of money?

Actually you're not clear enough.

Can you post the table definition, some sample data along with the expected result you want from this procedure?

Do you have a distributed transaction opened?

You have 11 scans in this query... no wonder why it's so slow.

Can you rewrite the query so it uses ansi92 standards?

(Select 1 from a inner join b on a.id...

What about it?

Never seen that!!

You can replace this whole proc with a single query.

What exactly are you trying to do?

This (convert(varchar(50),ContractDate,101)) will always force a scan to be executed. You'll kill any performance in that query right from the bat.

Just paste the text here.

set showplan_text on

go

--query goes here

go

set showplan_text oFF

That would qualify as a Whoops .

Be carefull with that technic... You could be losing performance if you are nesting too many queries.

Can you use the real inner join syntaxe. You might have a big fat series of cross joins in that query.

Can you post the execution plan?

Restored with the wrong backup??

Try to catch the parameters that cause the wrong execution plan. You'll most likely see a big difference in teh execution plan (scan instead of seek).

Do a search on...