Skip to content

Instantly share code, notes, and snippets.

@ststeiger
Last active October 6, 2015 14:59
Show Gist options
  • Select an option

  • Save ststeiger/23d929aabac37a6a5a7a to your computer and use it in GitHub Desktop.

Select an option

Save ststeiger/23d929aabac37a6a5a7a to your computer and use it in GitHub Desktop.
Filter IN on "array" in SQL-Server
DECLARE @SO_UIDs varchar(MAX);
SET @SO_UIDs = 'F2808163-C192-4860-9C32-2514F880F123,DDCC699D-B525-4DA3-A558-3BE87AA8923A,043AB989-AA6C-4621-AF26-44C9D537483B,DB14B864-6F96-41B9-BD22-51EAEABC46B9,60DF0FF4-AC1E-4526-8511-653F07CB19C3';
-- SET @SO_UIDs = '81f82b66-5cc1-4985-869f-cade014fb4a4';
-- SET @SO_UIDs = NULL
-- SET @SO_UIDs = '00000000-0000-0000-0000-000000000000';
DECLARE @tXML xml;
SET @tXML = CAST(('<e>' + REPLACE(NULLIF(@SO_UIDs, '00000000-0000-0000-0000-000000000000'), ',', '</e><e>') + '</e>') AS xml);
-- PRINT CAST(@tXML AS nvarchar(4000))
DECLARE @numFilterItems int;
SET @numFilterItems = (SELECT COUNT(nodeList.ele.value('.', 'varchar(36)')) FROM @tXML.nodes('e') AS nodeList(ele));
-- PRINT @numFilterItems
SELECT
T_AP_Standort.SO_UID
,T_AP_Standort.SO_Nr
,T_AP_Standort.SO_Bezeichnung
FROM T_AP_Standort
WHERE (1=1)
AND
(
T_AP_Standort.SO_UID IN
(
SELECT nodeList.ele.value('.', 'varchar(36)') FROM @tXML.nodes('e') AS nodeList(ele)
)
OR @numFilterItems = 0
)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment