LIKE Operator with Parameter

• dhunted2000

  Old Hand

  Points: 344

  More actions

  January 7, 2009 at 5:04 am

  #155096

  Hi guys,

  got a problem here,cant get this to work

  DECLARE @FIELD CHAR(50)

  DECLARE @VALUE CHAR(4)

  SET @FIELD='Name'

  SET @VALUE='MEDIC'

  SELECT [Supplier],[Name] FROM scheme.plsuppm Where @FIELD like @VALUE

  I have 10 records of Names with MEDICAL words in them..

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  January 7, 2009 at 5:19 am

  #922444

  dhunted2000 (1/7/2009)

  ---

  Hi guys,

  got a problem here,cant get this to work

  DECLARE @FIELD CHAR(50)

  DECLARE @VALUE CHAR(4)

  SET @FIELD='Name'

  SET @VALUE='MEDIC'

  SELECT [Supplier],[Name] FROM scheme.plsuppm Where @FIELD like @VALUE

  I have 10 records of Names with MEDICAL words in them..

  You can't use a variable to represent a part of the statement, only values. So this will work:

  --DECLARE @FIELD CHAR(50)

  DECLARE @VALUE CHAR(4)

  --SET @FIELD='Name'

  SET @VALUE='MEDIC' + '%'

  SELECT [Supplier], [Name] FROM scheme.plsuppm Where [Name] like @VALUE

  If you really must build the statement using a variable, then you will have to use dynamic sql:

  DECLARE @Column VARCHAR(20), @VALUE VARCHAR(20), @Sql VARCHAR(200)

  SET @Column = '[Name]'

  SET @VALUE = 'MEDIC' + '%'

  SET @Sql = 'SELECT [Supplier], [Name] FROM scheme.plsuppm WHERE ' + @Column + ' LIKE ''' + @VALUE + ''''

  PRINT @Sql

  --EXEC (@Sql) -- OR sp_executesql

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• dhunted2000

  Old Hand

  Points: 344

  More actions

  January 7, 2009 at 7:03 am

  #922498

  got it to work..

  anyway I used this..

  DECLARE @VALUE NVARCHAR(5)

  SET @VALUE='384'

  SELECT [Supplier],[Name] FROM scheme.plsuppm Where [Name] like '%' + @VALUE + '%'

  Thanks..

• Vladan

  SSC-Insane

  Points: 22072

  More actions

  January 7, 2009 at 8:00 am

  #922549

  That's of course much better than using dynamic SQL. Dynamic SQL should be used only where it is necessary.