Stored Procedure LIKE not working

• Mark Selby

  Grasshopper

  Points: 10

  More actions

  February 16, 2007 at 5:56 am

  #172130

  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

• Ken McKelvey

  SSCoach

  Points: 18902

  More actions

  February 16, 2007 at 6:15 am

  #690184

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

  LIKE '%' + @myvar

  or

  LIKE '%' + @myvar + '%'

  depending on what you want

   

• Mark Selby

  Grasshopper

  Points: 10

  More actions

  February 16, 2007 at 6:25 am

  #690187

  Desparation really!

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