Stored Procedure LIKE not working

  • I have the following simple stored procedure:

    Create Procedure SP_MySP

    (

    @myvar text

    )

    AS

    SELECT * FROM MyTable WHERE Col1 LIKE '% @myvar'

    GO

    Sadly this is not working. My guess is that enclosing @myvar in single brackets makes it behave literally and does not get substituted for the value of the variable passed to the procedure. Removing the % doesn't help either. Have also tried:

    '%' + @myvar

    and

    '% " & @myvar & " '

    and many others, but nothing seems to work.

    The only thing that does work is if I hard code the variable value into the procedure (e.g. '% The String')

    If anyone can help, it would be much appreciated.

    Thanks

    Mark

  • @myvar varchar(7999) --!! What made you think you could have a text parameter?

    LIKE '%' + @myvar

    or

    LIKE '%' + @myvar + '%'

    depending on what you want

     

  • Desparation really!

    Have changed the type from text to varchar and now works as I wanted. Thanks very much for such a speedy reply

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply