Wildcards in SQL Server stored procedures

[Ralph Noble]

I thought this problem would go away over the Christmas holiday, but
of course it did not. I'm trying to write a stored procedure
incorporating wildcards, so I can search for variations. Example, if
name 'Smith' is submitted, sproc should retrieve all records
containing 'John Smith', 'Zenia Smith', 'Smithfield & Co.' You get the
idea.

Using SQL Query Analyzer, the query

select * from file
where name like '%smith%'

works like a charm.

But if I write a stored procedure declaring the variable @name and
using a where clause 'where name like '%@name%'', I get zero results.
The query doesn't bomb. It just doesn't produce anything - even though
I know there are records that meet the criteria.

Any ideas? Or are sprocs and wildcards incompatible?

[Erland Sommarskog]

Ralph Noble (ra*********@ho tmail.com) writes:

But if I write a stored procedure declaring the variable @name and
using a where clause 'where name like '%@name%'', I get zero results.
The query doesn't bomb. It just doesn't produce anything - even though
I know there are records that meet the criteria.

Insert someone called John @nameson (yes, with at-sign and all) and you will
get a result.

Unlike languages like Perl or Unix Shells there is interpolation of
variables in T-SQL, so you need to write:

LIKE '%' + @name + '%'
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp