variables and Escape quotes

Question

variables and Escape quotes

TJT

SSCommitted

Points: 1926
More actions
May 15, 2013 at 8:33 am

#277541

I am trying to create a variable like the following in a WHERE clause
DECLARE @test-2 AS VARCHAR(90)
SET @test-2 = '2003 OR Cars.year = 2004 OR Cars.year = 2004'
SELECT Cars.year
FROM CARS
WHERE Cars.year = @test-2
Is this possible to set a variable like this? I need to escape the "OR Cars.year =" part it seems

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

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

TJT (5/15/2013)
I am trying to create a variable like the following in a WHERE clause
DECLARE @test-2 AS VARCHAR(90)
SET @test-2 = '2003 OR Cars.year = 2004 OR Cars.year = 2004'
SELECT Cars.year
FROM CARS
WHERE Cars.year = @test-2
Is this possible to set a variable like this? I need to escape the "OR Cars.year =" part it seems

No, it's invalid T-SQL construction.

You should use the following:

SELECT Cars.year

FROM CARS

WHERE Cars.year IN (2003,2004,2005)

or you can build the whole statement as dynamic SQL and execute it, however I don't think that is really applicable for your case.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Steven Willis SSCrazy Eights Points: 9893 More actions · Answer 2

I would use Jeff Moden's DelimitedSplit8K function to do this:

First create some sample data:

IF OBJECT_ID('tempdb..#Cars') IS NOT NULL

DROP TABLE #Cars

CREATE TABLE #Cars (

[ID] INT IDENTITY(1,1) NOT NULL,

[CarBrand] VARCHAR(10) NULL,

[CarYear] INT NULL,

PRIMARY KEY (ID))

INSERT INTO #Cars

SELECT 'Chevrolet',2003 UNION ALL

SELECT 'Ford',2005 UNION ALL

SELECT 'Toyota',2006 UNION ALL

SELECT 'Ford',2012 UNION ALL

SELECT 'Saturn',2008 UNION ALL

SELECT 'Honda',2005 UNION ALL

SELECT 'Chevrolet',2004

SELECT * FROM #Cars

This is how to filter using DelimitedSplit8k:

DECLARE @test-2 AS VARCHAR(90)

SET @test-2 = '2003,2004,2005'

SELECT

c.CarBrand

,c.CarYear

FROM

#Cars c

INNER JOIN

dbo.DelimitedSplit8k(@Test,',') as dsk

ON c.CarYear = dsk.Item

Output

CarBrandCarYear

Chevrolet2003

Chevrolet2004

Ford2005

Honda2005