VEMsispS

1CREATE TABLE dbo.DataSet
2(
3    DatasetID INT NOT NULL
4    , DatasetName VARCHAR(100) NOT NULL
5);
6
7INSERT INTO dbo.DataSet (DatasetID, DatasetName)
8VALUES(1, 'DS1')
9    , (2, 'DS2')
10    , (3, 'DS3')
11    , (4, 'DS4');
12
13CREATE TABLE dbo.Data
14(
15    DatasetID INT NOT NULL
16    , ProductID INT NOT NULL
17);
18
19INSERT INTO dbo.Data (DatasetID, ProductID)
20VALUES(1, 1)
21    , (1, 2)
22    , (1, 3)
23    , (2, 4)
24    , (2, 5)
25    , (2, 6)
26    , (3, 7)
27    , (3, 8)
28    , (4, 9);
29	
30CREATE PROCEDURE dbo.GetDatasets
31(
32    @ProductIDs VARCHAR(1000)
33)
34AS
35BEGIN
36    SET NOCOUNT ON;
37
38    SELECT DS.DatasetID, DS.DatasetName, D.ProductID
39    INTO #Table
40    FROM dbo.DataSet AS DS
41    INNER JOIN dbo.Data AS D
42        ON D.DatasetID = DS.DatasetID
43    INNER JOIN dbo.SplitString(@ProductIDs, ',') AS SS
44        ON SS.value = D.ProductID;
45
46    IF  EXISTS (SELECT * FROM #Table AS T WHERE T.DatasetID = 1)
47    AND EXISTS (SELECT * FROM #Table AS T WHERE T.DatasetID = 2)
48    BEGIN
49        INSERT INTO #Table (DatasetID, DatasetName, ProductID)
50        SELECT 0, 'Unified dataset', T.ProductID
51        FROM #Table AS T
52        WHERE T.DatasetID IN (1, 2);
53    END
54
55    SELECT *
56    FROM #Table;
57END
58	
59EXECUTE dbo.GetDatasets @ProductIDs = '1,2,8';
60	
61DatasetID DatasetName ProductID
62--------- ----------- ---------
631         DS1         1
641         DS1         2
653         DS3         8
66	
67EXECUTE dbo.GetDatasets @ProductIDs = '1,2,4,8';
68	
69DatasetID DatasetName     ProductID
70--------- --------------- ---------
711         DS1             1
721         DS1             2
732         DS2             4
743         DS3             8
750         Unified dataset 1
760         Unified dataset 2
770         Unified dataset 4
78	
79INSERT INTO dbo.Data (DatasetID, ProductID)
80SELECT 0 AS DatasetID, D.ProductID
81FROM dbo.Data AS D
82WHERE D.DatasetID IN (1, 2);