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]

Re: Wildcards in SQL Server stored procedures

Ralph Noble (ralph_noble@ho tmail.com) writes:[color=blue]
> 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.[/color]

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, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp