November 8, 2011 at 12:54 pm
Hello.
I am trying to execute a query which contains what i am calling a 'dynamic 'IN' statement' (I am not sure if that is the correct vernacular or not). The dynamic statement may contain no values, 1 value, 2 values, etc. Ultimately I am hoping to build a stored procedure around the query which accepts the 'IN' statement as a parameter.
As an example of what I am trying to do, I have a test table with two columns: testId and testYear. In the query, I am using a variable @testYear and I want to be able to query based on any number of 'year' values. I can get the query to work when passing in 1 year value, but not when passing in more than 1. By 'not working' I mean that the query executes successfully, but no rows are returned.
Any ideas on how to format the variable @testYear correctly so that the query returns the correct number of rows? Or how to better approach the problem?
Thank you.
Create TABLE dbo.test
(
testId int,
testYear char(4)
)
INSERT into dbo.test values (1, '2009')
INSERT into dbo.test values (2, '2010')
INSERT into dbo.test values (3, '2008')
INSERT into dbo.test values (4, '2009')
INSERT into dbo.test values (5, '2007')
INSERT into dbo.test values (6, '2009')
INSERT into dbo.test values (7, '2008')
INSERT into dbo.test values (8, '2008')
INSERT into dbo.test values (9, '2010')
INSERT into dbo.test values (10, '2009')
DECLARE @testYear varchar(50) = null
-- this query works; two rows are returned.
SET @testYear = '2010'
SELECT * FROM dbo.test WHERE ( @testYear is null OR testYear IN (@testYear) )
--this does not work; query executes but no rows returned.
SET @testYear = '2010, 2009'
SELECT * FROM dbo.test WHERE ( @testYear is null OR testYear IN (@testYear) )
--this does not work; query executes but no rows returned.
SET @testYear = ' ''2010'', ''2009'' '
SELECT * FROM dbo.test WHERE ( @testYear is null OR testYear IN (@testYear) )
--this does not work (grasping at straws here); query executes but no rows returned.
SET @testYear = CAST(2010 as CHAR(4)) + ',' + CAST(2009 as CHAR(4))
SELECT * FROM dbo.test WHERE ( @testYear is null OR testYear IN (@testYear) )
--this works (again, kinda grasping at straws); 6 rows returned.
SELECT * FROM dbo.test WHERE ( @testYear is null OR testYear IN (SELECT 2010 UNION ALL SELECT 2009) )
-- this does not work; ; query executes but no rows returned.
SET @testYear = 'SELECT 2010 UNION ALL SELECT 2009'
SELECT * FROM dbo.test WHERE ( @testYear is null OR testYear IN (@testYear) )
November 8, 2011 at 1:12 pm
You can approach this a number of ways. One way is to pass in a table variable containing the years to be selected.
DECLARE @inputYears table (getyear char(4) primary key)
INSERT INTO @inputYears
values ('2009'),('2010')
select *
from dbo.test t
join @inputYears y on testYear = getyear
-- or
select *
from dbo.test t
where testYear in (select getyear from @inputYears)
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 8, 2011 at 1:15 pm
You'll want to pass in the values you want to check against in a single string and use a string splitting method (usually in a user defined function) to split them out into a table. You would then either join on that table or return all if the string is null.
For really good info about string splitting, you should read this article by Jeff Moden: http://www.sqlservercentral.com/articles/Tally+Table/72993/.
**edit: Dixie Flatline gives another good option as well.
November 8, 2011 at 1:33 pm
If you can use them, Table Valued Parameters (TVPs) are a new feature in SQL 2008 which do this very, very well. It's basically what Dixie Flatline suggested, but you'll find the documentation on them if you search "t-sql table value parameter" in Bing or Google or whatever.
If not, then parsing out a delimited string is the usual method, as mentioned by bt.
The TVP method is usually much, much faster.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 8, 2011 at 4:14 pm
Thank you all for your replies and suggestions.
I am going to jump down the Table Valued Parameters rabbit hole to see where that leads.
Thanks again.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply