Different Plans With Literal String Vs. Param

The proc below does two queries that are functionally identical. The only difference is that one LIKE 'foo%', and the other uses LIKE @searchText, where @searchText = 'foo%'.

But the first does an index seek, and the second does an index scan -- and it makes a big difference in performance. (Timing stats are below.)

How can I make the second query seek instead of scanning?


CREATE PROCEDURE test_like

@searchText nvarchar(64)

AS


-- Straight literal string search

SELECT companyId

FROM companies

WHERE searchbrand LIKE 'foo%'




-- With param

SELECT companyId

FROM companies

WHERE searchbrand LIKE @searchText

OPTION(OPTIMIZE FOR(@searchText = 'foo%'))

GO


EXEC test_like 'foo%'




-- Query 1:

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.


-- Query 2:
SQL Server Execution Times:

CPU time = 47 ms, elapsed time = 40 ms.