Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

variables and Escape quotes Expand / Collapse
Author
Message
Posted Wednesday, May 15, 2013 8:33 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 7:04 AM
Points: 37, Visits: 103
I am trying to create a variable like the following in a WHERE clause

DECLARE @Test AS VARCHAR(90)
SET @Test = '2003 OR Cars.year = 2004 OR Cars.year = 2004'

SELECT Cars.year
FROM CARS
WHERE Cars.year = @Test


Is this possible to set a variable like this? I need to escape the "OR Cars.year =" part it seems
Post #1453130
Posted Wednesday, May 15, 2013 8:37 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 8:55 AM
Points: 2,873, Visits: 5,185
TJT (5/15/2013)
I am trying to create a variable like the following in a WHERE clause

DECLARE @Test AS VARCHAR(90)
SET @Test = '2003 OR Cars.year = 2004 OR Cars.year = 2004'

SELECT Cars.year
FROM CARS
WHERE Cars.year = @Test


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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1453134
Posted Wednesday, May 15, 2013 11:47 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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 AS VARCHAR(90)
SET @Test = '2003,2004,2005'

SELECT
c.CarBrand
,c.CarYear
FROM
#Cars c
INNER JOIN
dbo.DelimitedSplit8k(@Test,',') as dsk
ON c.CarYear = dsk.Item


Output


CarBrand CarYear
Chevrolet 2003
Chevrolet 2004
Ford 2005
Honda 2005


Post #1453243
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse