query execution plan changes when code is in an SP

[francisT]

Hi

I am using SQL Server 2000 Enterprise Edition.
When I look at the Query execution plan (QEP)
for the following T-SQL statements:

DECLARE @TT int
DECLARE @CC varchar(20)
SET @TT = 1
SET @CC = '0'
SELECT V
FROM VIEW1
WHERE T = @TT AND C = @CC

the query optimiser finds an efficient plan that uses
the clustered index that I have created for the indexed view VIEW1.
However, when I put the above T-SQL statements into a stored procedure, the query optimiser does NOT use the
clustered index on the view. It ignores the indexed view and joins the underlying tables. This QEP takes 500
milliseconds to execute, whereas the QEP that uses the
clustered index on the indexed view takes half of
millisecond.

I have tried various tricks to persuade the query optimiser
to use the indexed view. For example, I have used NOEXPAND
and I have given the index name as a hint. I have also tried creating non-clustered indexes on the columns
named in the query and a composite index that 'covers'
the query. The query optimiser steadfastly ignores all
of these attempts to persuade it to use the clustered
index.

Can anyone spot what I am doing wrong?

Regards

francisT