CODEWITHSUNDEEP
sql-serversql-server-2008stored-procedures
qinking126
qinking126

Reputation: 11885

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

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

Upvotes: 2

Views: 2195

Answers (2)

Kavit Trivedi
Kavit Trivedi

Reputation: 129

Correct way is below: 

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

Upvotes: 0

Alex K.
Alex K.

Reputation: 175766

Simply;

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

Your just adding 2 constant strings to a variable.

Upvotes: 6

Related Questions