Click here to Skip to main content
15,936,014 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I have a table as below
ID	CustomerID	Type	Value
1	1			T1		123
2	1			T2		XYZ
3	1			T1		456
4	2			T1		789
5	1			T2		ABC
6	4			T1		111

My request to SP will have value in variable as

@SPInput = 'T1,123|T2,ABC|T1,456' --(It can have any combinations of Types and Values).

Value in "Type" column cannot be predicted and can be till 200 or may be 300.

If I get 2 values for T1 in request, then Logical OR operator should be performed in the table


If I get another value say T2 in request, then Logical AND operator should be performed in table for T1 AND T2

There can be any combinations of Type and Values

I need CustomerID if all the values matches with my input request.

What I have tried:

---------------- ClueLess ----------------
Updated 7-Sep-20 23:02pm

I'm not sure i understand you well...

You need to split @SPInput by {'|', ','} to get [Type] and [Value]. Then you have to compare that values with the values in a table. See:

DECLARE @tmp TABLE(ID int,	CustomerID int,	[Type] varchar(30),	[Value] varchar(30))

INSERT INTO @tmp(ID, CustomerID, [Type],  [Value])
VALUES(1, 1, 'T1', '123'),
(2, 1, 'T2', 'XYZ'),
(3,	1, 'T1', '456'),
(4,	2, 'T1', '789'),
(5,	1, 'T2', 'ABC'),
(6,	4, 'T1', '111')

DECLARE @SPInput varchar(150) = 'T1,123|T2,ABC|T1,456'

--split @SPInput by '|'
;WITH KeyPairs AS
	--initial query
	SELECT LEFT(@SPInput, CHARINDEX('|', @SPInput)-1) KeyPair, RIGHT(@SPInput, LEN(@SPInput)-CHARINDEX('|', @SPInput)) Remainder
	--recursive part
	SELECT LEFT(Remainder, CHARINDEX('|', Remainder)-1) KeyPair, RIGHT(Remainder, LEN(Remainder)-CHARINDEX('|', Remainder)) Remainder
	FROM KeyPairs
	WHERE CHARINDEX('|', Remainder)>0
	SELECT Remainder KeyPair, NULL Remainder
	FROM KeyPairs
	WHERE CHARINDEX('|', Remainder)=0
), TV AS --split KeyPair by ','
	SELECT LEFT(KeyPair, CHARINDEX(',', KeyPair)-1) [Type], RIGHT(KeyPair, LEN(KeyPair)-CHARINDEX(',', KeyPair)) [Value]
	FROM KeyPairs
	WHERE CHARINDEX(',', KeyPair)>0
-- get data from @tmp table where type and value passed by @SPInput are equal
FROM @tmp t1 INNER JOIN TV t2 ON t1.[Type] = t2.[Type] AND t1.[Value] = t2.[Value]

ID	CustomerID	Type	Value
1	1	T1	123
3	1	T1	456
5	1	T2	ABC

In above example i'm using CTE[^] to split input into parts, but you can also use different way: Splitting Delimited Strings Using XML in SQL Server[^]

Now, you have to change the code to your needs.

Good luck!

As to our discussion (in comments) to the condition: (T1 AND (123 OR 456)) AND (T2 AND BC)...
The simplest way to filter data based on above condition is to use client code (c#,, etc.). See:
//crate sample data
DataTable tmp = new DataTable();
tmp.Columns.AddRange(new DataColumn[]
		new DataColumn("ID", typeof(int)),
		new DataColumn("CustomerID", typeof(int)),
		new DataColumn("Type", typeof(string)),
		new DataColumn("Value", typeof(string))
tmp.Rows.Add(new object[]{1, 1, "T1", "123"});
tmp.Rows.Add(new object[]{2, 1, "T2", "XYZ"});
tmp.Rows.Add(new object[]{3, 1, "T1", "456"});
tmp.Rows.Add(new object[]{4, 2, "T1", "789"});
tmp.Rows.Add(new object[]{5, 1, "T2", "ABC"});
tmp.Rows.Add(new object[]{6, 4, "T1", "111"});
//determine values to find
KeyValuePair<string, string>[] values2find = new KeyValuePair<string, string>[]
		new KeyValuePair<string, string>("T1", "123"),
		new KeyValuePair<string, string>("T2", "BC"),
		new KeyValuePair<string, string>("T1", "456")
//group values to find by its Type
var filter = values2find
//filter data 
var result = tmp.AsEnumerable()
	.Where(x=> filter.All(y=>y.Key==x.Field<string>("Type") && y.Any(z=>z.Value==x.Field<string>("Value"))))
Console.WriteLine($"Found {result.Count()} result(s)");
foreach(var f in result)
	Console.WriteLine($"Type: {f.Field<string>("Type")}\tValue:{f.Field<string>("Value")}");

Found 0 result(s)

For further details, please see:
Enumerable.All<TSource>(IEnumerable<TSource>, Func<TSource,Boolean>) Method (System.Linq) | Microsoft Docs[^]
Enumerable.Any Method (System.Linq) | Microsoft Docs[^]
Share this answer
Vishal0903 8-Sep-20 3:55am    
If I pass "DECLARE @SPInput varchar(150) = 'T1,123|T2,BC|T1,456'"

It should not return any result - As my final Output is T1 and T2

but T2 -> BC is not present in my table

This query almost works, but except for AND - Can you please help with this?
Maciej Los 8-Sep-20 4:12am    
"It should not return any result..."
Why? You've passed 3 pairs: {{T1, 123}, {T2, BC}, {T1, 456}}. Two of them exist in a table: {{T1, 123}, {T1, 456}}...
Vishal0903 8-Sep-20 4:14am    
The records may or may not be present in the table - Only after querying the table, I will be able to know if the value exists
Vishal0903 8-Sep-20 4:13am    
Logical OR when Same Type has 2 values
Logical AND when Different Type is present


Ex - (123 OR 456) AND (BC)
Maciej Los 8-Sep-20 4:15am    
If you would like to build much complicated conditions to filter data, i'd suggest to do it on client code (c#,, etc.), NOT on server code (t-sql).
That's probably not a good idea: you would need to break the string up (which isn't impossible: Converting comma separated data in a column to rows for selection[^] shows you the basics of how to do that) then use those separated elements to create a WHERE clause for a new SELECT query, and then EXEC that query.

Which can be done, though it's a nasty job and going to be a PITA to maintain and test all the edge cases.

The problem is that you're going to be writing an SP that leaves your DB wide open to SQL Injection due to the need to construct a command string and execute it. I'd really think very, very hard before I started on this: it could be very dangerous in the real world.
Share this answer
You can create a table function with the logic below and join the result to the table you want to filter from.

Better option would be to use user data types to pass the values through a user defined type.

Declare @SPInput NVARCHAR(MAX)= 'T1,123|T2,ABC|T1,456'

DECLARE @SetSeparator NVARCHAR(1) = '|'
DECLARE @ValSeparator NVARCHAR(1) = ','

 DECLARE @StartIndex INT, @TypeEndIndex INT,@ValStartIndex INT, @EndIndex INT

      TypeName NVARCHAR(MAX),
      SET @StartIndex = 1
      IF SUBSTRING(@SPInput, LEN(@SPInput) - 1, LEN(@SPInput)) <> @SetSeparator
            SET @SPInput = @SPInput + @SetSeparator
      WHILE CHARINDEX(@SetSeparator, @SPInput) > 0
            SET @EndIndex = CHARINDEX(@SetSeparator, @SPInput)
			SET @TypeEndIndex = CHARINDEX(@ValSeparator, @SPInput)

			DECLARE @SubSet NVARCHAR(MAX) = SUBSTRING(@SPInput, @StartIndex, @EndIndex - 1);
            INSERT INTO @Output(TypeName,Val)
            SELECT SUBSTRING(@SubSet, @StartIndex, @TypeEndIndex - 1),SUBSTRING(@SubSet, @TypeEndIndex + 1, @EndIndex - 1)
            SET @SPInput = SUBSTRING(@SPInput, @EndIndex + 1, LEN(@SPInput))

	  FROM @Output
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900