sp_executesql with parameter for LIKE expression

• kamau.john

  SSC Enthusiast

  Points: 192

  More actions

  September 17, 2009 at 5:06 am

  #141747

  Hi,

  Is it possible to send a parameter for a LIKE expression to sp_executesql?

  E.g.

  Declare @paramdef nvarchar(500)

  Declare @sql nvarchar(2000)

  Set @paramdef = N'@ss varchar(100)

  Set @sql = 'Select * from Employees Where Name LIKE '%@ss%'

  sp_executesql @sql,@paramdef,@ss='partialname'

  I've tried all combinations of setting that parameter, but it doesnt seem to work.

  I want it so that the search is for any match,i.e. using %.

  Thanks in advance

  John

• kamau.john

  SSC Enthusiast

  Points: 192

  More actions

  September 17, 2009 at 5:21 am

  #1054901

  Nevermind, figured it out. I"ll wrap the search param with % before suppling sp_executesql.

• HansVE

  Mr or Mrs. 500

  Points: 524

  More actions

  September 17, 2009 at 7:45 am

  #1054985

  kamau.john (9/17/2009)

  ---

  Nevermind, figured it out. I"ll wrap the search param with % before suppling sp_executesql.

  Or you can wrap the param in the dynamic code.

  Set @sql = 'SET @ss = ''%'' + @ss + ''%'';Select * from Employees Where Name LIKE @ss;'