February 10, 2016 at 1:10 pm
Is there a way to pass a comma separated list as a parameter in a where clause? I am taking a basic comma separated list 'aaa, bbb, ccc' and formatting it in the way that would appear when using in ('aaa', 'bbb', 'ccc') and setting that string as a parameter.
If I just do a select @items I get 'aaa', 'bbb', 'ccc' which is what I would expect.
However...
--Does not work
SELECT *
FROM items
WHERE itemCode IN (@items)
--Works
SELECT *
FROM items
WHERE itemCode IN ('aaa', 'bbb', 'ccc')
February 10, 2016 at 1:18 pm
When you use WHERE ItemCode IN (@Items), SQL Server considers @Items to be a single value. IN (@Item1, @Item2, @Item3) would work, but then you first need to split the list that was passed in into the separate variables.
Further reading with lots of options and comparisons is here: http://www.sommarskog.se/arrays-in-sql.html
February 10, 2016 at 3:21 pm
If you're interested in a high-performance string splitter, invest the time to read Jeff Moden's article at http://www.sqlservercentral.com/articles/Tally+Table/72993/. It's high-performance splitter function and the article contains lots of ancillary learning. The article isn't short, but it's definitely worth the time to read and digest. Be forewarned that it can change the way you look at data in a good way. It can also change your expectations of performance.
February 10, 2016 at 8:00 pm
Here is a CLR string splitter from Adam Machanic. Original blog post.
----------------------------------------------------------------------------------------------------------------------------------------
-- drop objects
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.SplitStringCLR')
AND type IN (N'FN', N'IF', N'TF', N'FS', N'FT') )
DROP FUNCTION dbo.SplitStringCLR ;
GO
----------------------------------------------------------------------------------------------------------------------------------------
-- drop assembly
IF EXISTS (SELECT *
FROMsys.assemblies
WHEREname = 'Split' )
DROP ASSEMBLY [Split];
GO
----------------------------------------------------------------------------------------------------------------------------------------
-- create assembly
CREATE ASSEMBLY [Split]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C01030024E9B34D0000000000000000E00002210B010800000E00000006000000000000AE2D0000002000000040000000004000002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000582D000053000000004000009003000000000000000000000000000000000000006000000C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000B40D000000200000000E000000020000000000000000000000000000200000602E7273726300000090030000004000000004000000100000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001400000000000000000000000000004000004200000000000000000000000000000000902D0000000000004800000002000500E0210000780B00000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000A2026F1300000A2D0E026F1400000A0373040000062B0C168D140000011673040000068C030000022A000000133002001A0000000100001102A5040000020A031200280800000654041200280A000006512A1E02281500000A2AC202037D0100000402047D0300000402027B010000048E697D02000004027C05000004FE150400000202167D040000042A0013300500C200000002000011027B04000004027B020000043302162A027B040000040A2B56027B010000040693027B030000043342027C0500000425280800000617582809000006027C05000004027B01000004027B0400000406027B0400000459731700000A280B000006020617587D04000004172A0617580A06027B0200000432A1027C0500000425280800000617582809000006027C05000004027B01000004027B04000004027B02000004027B0400000459731700000A280B00000602027B020000047D04000004172A32027B050000048C040000022A1A731800000A7A1E027B060000042A2202037D060000042A1E027B070000042A2202037D070000042A42534A4201000100000000000C00000076322E302E35303732370000000005006C000000CC030000237E0000380400008404000023537472696E677300000000BC0800000800000023555300C4080000100000002347554944000000D4080000A402000023426C6F6200000000000000020000015717A2030902000000FA253300160000010000001900000004000000070000000B00000009000000010000001900000012000000020000000200000003000000050000000300000001000000020000000200000000000A00010000000000060052004B00060059004B000600760063000A00A3008E000A0029020E0206007102520206009B0289020600B20289020600CF0289020600EE02890206000703890206002003890206003B03890206005603890206006F0352020600830389020600BC039C030600DC039C030A00FA030E02060024044B0006002904520206003F04520206004A044B00060051044B00060069049C03000000000100000000000100010001001000140000000500010001000B011000290000000900010004000B01100039000000090006000800210053012E0021005901320021006001350001006A0132000100700138000100CA0132000100E40152005020000000009600AC000A0001007C20000000009600B20012000300A220000000008618BA001B000600AA20000000008318BA001F000600DC2000000000E101C00026000800AA2100000000E109F1002A000800B72100000000E10128011B000800BE210000000083089E0140000800C621000000008308AB0144000800CF21000000008308B80149000900D721000000008308C1014D000900000001000802000002003B02000001004502020002004902020003007E02000001000802000002003B0200000100830200000100830203000D001900E800260019001C012A0019004D011B002900BA001B003100BA001B003900BA004D004100BA004D004900BA004D005100BA004D005900BA004D006100BA004D006900BA004D007100BA004D007900BA0070008100BA004D008900BA0044009100BA001B009900BA001B0021000F04260021001A040D020900BA001B00A900BA001702B900BA001D02C100BA001B00C900BA001B00200093007500240023005D002E0033002E022E00430045022E008B0084022E004B004B022E0053002E022E00730045022E003B0045022E0083007B022E005B005A022E0063004502C100CB002902E100CB0029020001CB0029022001CB0029024001CB0029026001CB002902120225020300010004000200000077013C000000FA01550000000302590002000600030001000900050002000800050002000A00070001000B00070003000A00030003000C00050003000E00070004800000010000002310FA9B000000000000AC00000002000000000000000000000001004200000000000200000000000000000000000100820000000000030002000400020000000000003C4D6F64756C653E0053706C69742E646C6C0055736572446566696E656446756E6374696F6E730053706C6974456E756D657261746F720053706C6974526F77006D73636F726C69620053797374656D004F626A6563740056616C7565547970650053797374656D2E436F6C6C656374696F6E730049456E756D657261746F720053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C43686172730053706C69740046696C6C526F77002E63746F720053797374656D2E436F6C6C656374696F6E732E49456E756D657261746F722E4D6F76654E657874004D6F76654E6578740053797374656D2E436F6C6C656374696F6E732E49456E756D657261746F722E6765745F43757272656E74006765745F43757272656E740053797374656D2E436F6C6C656374696F6E732E49456E756D657261746F722E526573657400526573657400696E707574006C656E6774680064656C696D69746572007374617274007265636F72640053797374656D2E436F6C6C656374696F6E732E49456E756D657261746F722E43757272656E74006765745F53657175656E6365007365745F53657175656E6365006765745F4974656D007365745F4974656D003C53657175656E63653E6B5F5F4261636B696E674669656C64003C4974656D3E6B5F5F4261636B696E674669656C640053657175656E6365004974656D00496E707574004D6963726F736F66742E53716C5365727665722E5365727665720053716C46616365744174747269627574650044656C696D69746572006F626A0073657175656E63650053797374656D2E52756E74696D652E496E7465726F705365727669636573004F7574417474726962757465006974656D0076616C75650053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C7475726541747472696275746500436F6D56697369626C6541747472696275746500417373656D626C7956657273696F6E4174747269627574650053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650053716C46756E6374696F6E417474726962757465006765745F49734E756C6C006765745F56616C75650043686172005374727563744C61796F7574417474726962757465004C61796F75744B696E6400537472696E67004E6F74496D706C656D656E746564457863657074696F6E00436F6D70696C657247656E657261746564417474726962757465000003200000000000901C02C60145A34EACF1D06C744C88640008B77A5C561934E089070002120D121103080003011C1008100E03200001062002011D0303032000020320001C03061D03020608020603030611100328001C0320000804200101080320000E042001010E02060E032800080328000E12010001005408074D617853697A65FFFFFFFF04200101028196010006005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A446174614163636573730000000054557F4D6963726F736F66742E53716C5365727665722E5365727665722E53797374656D446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038391053797374656D446174614163636573730000000054020F497344657465726D696E69737469630154020949735072656369736501540E1146696C6C526F774D6574686F644E616D650746696C6C526F77540E0F5461626C65446566696E6974696F6E2173657175656E636520494E542C206974656D204E564152434841522834303030290420001D030407011110052001011159072003011D0308080307010804010000001601001153716C53657276657250726F6A6563743100000501000000000E0100094D6963726F736F667400002001001B436F7079726967687420C2A9204D6963726F736F6674203230313100000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F77730100802D000000000000000000009E2D0000002000000000000000000000000000000000000000000000902D000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF25002040000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000380300000000000000000000380334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE0000010000000100FA9B231000000100FA9B23103F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00498020000010053007400720069006E006700460069006C00650049006E0066006F00000074020000010030003000300030003000340062003000000034000A00010043006F006D00700061006E0079004E0061006D006500000000004D006900630072006F0073006F006600740000004C0012000100460069006C0065004400650073006300720069007000740069006F006E0000000000530071006C00530065007200760065007200500072006F006A006500630074003100000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0034003100330031002E00330039003900330030000000000034000A00010049006E007400650072006E0061006C004E0061006D0065000000530070006C00690074002E0064006C006C0000005C001B0001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020004D006900630072006F0073006F006600740020003200300031003100000000003C000A0001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530070006C00690074002E0064006C006C000000440012000100500072006F0064007500630074004E0061006D00650000000000530071006C00530065007200760065007200500072006F006A006500630074003100000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0034003100330031002E00330039003900330030000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0034003100330031002E00330039003900330030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000B03D00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE
GO
----------------------------------------------------------------------------------------------------------------------------------------
-- create objects
CREATE FUNCTION [dbo].[SplitStringCLR](@Input [nvarchar](max), @Delimiter [nchar](1))
RETURNS TABLE (
[ItemNumber] [int] NULL,
[Item] [nvarchar](4000) NULL
) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [Split].[UserDefinedFunctions].[Split]
GO
Note that you need to enable the CLR before using the function:
EXEC sp_configure 'clr', 1;
RECONFIGURE;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 11, 2016 at 7:09 am
Thanks everyone!
I ended up just using a temp table and inserting the values into the temp table at the start of my query. I then used WHERE itemCode IN (select code from #items)
February 11, 2016 at 7:38 am
You may want to consider performance differences between using an IN and an INNER JOIN
select columns
from dbo.sometable
where ItemCode in (select Item from #tempTable)
-- AND --
select columns
from dbo.sometable st
inner
join #tempTable tt
on st.ItemCode = tt.Item
... may yield different performance benchmarks. The differences could vary bases on the number of items in the temp table, if the temp table uses that value as a primary key and if the permanent table has a SARGable index. STATISTICS IO and TIME can give you some of that information.
--Paul Hunter
February 11, 2016 at 7:45 am
SQLNightOwl (2/11/2016)
You may want to consider performance differences between using an IN and an INNER JOINselect columns
from dbo.sometable
where ItemCode in (select Item from #tempTable)
-- AND --
select columns
from dbo.sometable st
inner
join #tempTable tt
on st.ItemCode = tt.Item
... may yield different performance benchmarks. The differences could vary bases on the number of items in the temp table, if the temp table uses that value as a primary key and if the permanent table has a SARGable index. STATISTICS IO and TIME can give you some of that information.
It depends.
http://sqlinthewild.co.za/index.php/2010/04/27/in-exists-and-join-a-roundup/[/url]
Always study the execution plan, always test the alternative options, and always test on a set which is representative of production data and then some.
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply