QA vs sproc mystery

Here's a poser (at least to me!)
When I run a select statement in QA the statement uses available indexes
(Index Scan) and is quite fast, but when I take the same script, place it in
a sproc, and execute the sproc, it uses a Table Scan rather than an Index
S on the Juvenile table - killing performance.
Any pointers would be appreciated
here's the script:
Select
J.JuvenileID,
(J.JuvLName + ', ' + J.JuvFName + ' (' + convert(varchar(10), J.juvDOB, 1)
+ ')' ) as JuvFullName,
Convert(varchar(10), J.JuvDOB, 1) as JuvDOB
From Juvenile J
Inner Join Placement P on J.JuvenileID = P.JuvenileID
Where juvPlaSite = @.SitLocationCode
and juvPlaEndDate IS NULL
and J.JuvLName LIKE CASE When (@.JuvenileLNameBeginsWith = '' OR
@.JuvenileLNameBeginsWith = 'ALL') then J.JuvLName ELSE
@.JuvenileLNameBeginsWith + '%' END
Order By JuvFullNameWhat column(s) is indexed in Juvenile?|||Can you post the sp?
AMB
"Dazed and Confused" wrote:

> Here's a poser (at least to me!)
> When I run a select statement in QA the statement uses available indexes
> (Index Scan) and is quite fast, but when I take the same script, place it
in
> a sproc, and execute the sproc, it uses a Table Scan rather than an Index
> S on the Juvenile table - killing performance.
> Any pointers would be appreciated
> here's the script:
> Select
> J.JuvenileID,
> (J.JuvLName + ', ' + J.JuvFName + ' (' + convert(varchar(10), J.juvDOB, 1
)
> + ')' ) as JuvFullName,
> Convert(varchar(10), J.JuvDOB, 1) as JuvDOB
> From Juvenile J
> Inner Join Placement P on J.JuvenileID = P.JuvenileID
> Where juvPlaSite = @.SitLocationCode
> and juvPlaEndDate IS NULL
> and J.JuvLName LIKE CASE When (@.JuvenileLNameBeginsWith = '' OR
> @.JuvenileLNameBeginsWith = 'ALL') then J.JuvLName ELSE
> @.JuvenileLNameBeginsWith + '%' END
> Order By JuvFullName
>|||"Dazed and Confused" <Dazed and Confused@.discussions.microsoft.com> wrote in
message news:C09CDD00-02A2-4199-9A1B-8420771CDF43@.microsoft.com...
> Here's a poser (at least to me!)
> When I run a select statement in QA the statement uses available indexes
> (Index Scan) and is quite fast, but when I take the same script, place it
in
> a sproc, and execute the sproc, it uses a Table Scan rather than an Index
> S on the Juvenile table - killing performance.
Obviate parameter sniffing by assigning parameters to local variables and
using the local variables in the query.