Where clause in stored proc evaluating varchar 1

[mhenley]

For some reason this procedure returns 0 records:

CREATE PROCEDURE get_association_by_name
(
@association_name varchar(50)
)
AS
SELECT
association_id,
association_name,
association_acronym,
phone,
city,
state,
zip
FROM association
WHERE association_name like '%@association_name%'
ORDER BY
association_name
return (@@error)

It executes fine (no errors), but just returns an empty recordset. I cut and paste the select into query analyzer (substituting actual string for @association_name) and it returns the data no problem.

I'm fairly new to working with stored proc's, but have never had any problems with this type of thing before. The only difference from anything I have done in the past is this time my where clause is evaluating a varchar. Would an EXEC statement help? Any help is greatly appreciated.

Matt.

 

[TomSark]

It looks like your problem is that you are expecting the value in the variable, @association_name, to be substituted in the expression that isn't really an expression, but it's a string literal.

Change it to this:

CREATE PROCEDURE get_association_by_name
(
@association_name varchar(50)
)
AS

DECLARE @like_str varchar(60) -- new

SET @like_str = '%' + @association_name + '%' -- new

SELECT
association_id,
association_name,
association_acronym,
phone,
city,
state,
zip
FROM association
WHERE association_name like @like_str -- new
ORDER BY association_name

return (@@error)

 

[mhenley]

Thanks, Tom.