From: simon on
I have stored procedure with 2 parameters.
If I execute this stored procedure it takes 9.5 seconds:

exec dbo.proc1 'test', '20090115'

If I open the stored procedure, set parameters value and execute the
query, it takes 1 second:

declare @param 1 varchar(10), @dateC datetime
SET @param1='test'
SET @dateC='20090115'

SELECT * from ...........

Any idea, why the difference? The same code, the same parameters
value, the same connection, just the different way of execution. And
the execution plan is also different, why? What to do?

I have SQL2008 and database in SQL2000 mode.

Thank you,