sql server 2008 stored procedure, cannot use variable in single quote

Question

here's my stored procedure.

ALTER PROCEDURE [dbo].[SearchIngredients] 
    @Term NVARCHAR(50) 
AS
BEGIN
    SET NOCOUNT ON;

    SELECT  *
    FROM    dbo.FoodAbbrev
    WHERE   Name LIKE '%@Term%'
    ORDER BY Name

END

I kept getting no result back. I think its because I put the varaible in the single quote and probably DB thinks that is part of string, not a variable. tried a few other ways, still cannot fix it, please help me.

I also tried. still get nothing back.

    SET @Term = '''%' + @Term + '%'''

    SELECT  *
    FROM    dbo.FoodAbbrev
    WHERE   Name LIKE @Term
    ORDER BY Name

score 6 · Accepted Answer · answered Sep 12 '12 at 14:41

6

Simply;

set @Term = '%' + @Term + '%'

Your just adding 2 constant strings to a variable.

answered Sep 12 '12 at 14:41

Alex K.

171,639
30
264
288

score 0 · Answer 2 · edited May 09 '13 at 11:15

0

Correct way is below:

set @FieldValue = '''' + @FieldValue + ''''

edited May 09 '13 at 11:15

Jens Erat

37,523
16
80
96

answered May 09 '13 at 10:58

Kavit Trivedi

129
3
13

sql server 2008 stored procedure, cannot use variable in single quote

2 Answers2