LIKE Operator with Parameter

Question

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..

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

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

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 · Answer 2

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 · Answer 3

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