January 7, 2009 at 5:04 am
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..
January 7, 2009 at 5:19 am
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
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
January 7, 2009 at 7:03 am
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..
January 7, 2009 at 8:00 am
That's of course much better than using dynamic SQL. Dynamic SQL should be used only where it is necessary.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy