Click here to Skip to main content
14,694,320 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

AND

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 ----------------
Posted
Updated 8-Sep-20 0:02am
v2

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
	WHERE CHARINDEX('|', @SPInput)>0
	--recursive part
	UNION ALL
	SELECT LEFT(Remainder, CHARINDEX('|', Remainder)-1) KeyPair, RIGHT(Remainder, LEN(Remainder)-CHARINDEX('|', Remainder)) Remainder
	FROM KeyPairs
	WHERE CHARINDEX('|', Remainder)>0
	UNION ALL
	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
SELECT t1.*
FROM @tmp t1 INNER JOIN TV t2 ON t1.[Type] = t2.[Type] AND t1.[Value] = t2.[Value]


Result:
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!

[EDIT]
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#, vb.net, 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
	.GroupBy(x=>x.Key)
	.ToList();
//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"))))
	.ToList();
	
Console.WriteLine($"Found {result.Count()} result(s)");
foreach(var f in result)
	Console.WriteLine($"Type: {f.Field<string>("Type")}\tValue:{f.Field<string>("Value")}");


Result:
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[^]
   
v2
Comments
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
AND
Logical AND when Different Type is present

T1,123|T2,BC|T1,456

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#, vb.net, etc.), NOT on server code (t-sql).
Maciej Los 8-Sep-20 4:17am
   
So, a complete where statement should look like: (T1 AND (123 OR 456)) AND (T2 AND BC) Am i right?
Vishal0903 8-Sep-20 4:19am
   
Yes, this is how I am expecting

Based on this Output there are many other steps to be performed - So, handling in C# or VB.Net would be still more difficult
Maciej Los 8-Sep-20 4:59am
   
Sorry, but you're wrong. Please, see updated solution (after "EDIT" word).
Sandeep Mewara 22-Sep-20 5:34am
   
My +5 ... SQL Master! :)
Maciej Los 22-Sep-20 6:07am
   
Thank you, Sandeep.
Sandeep Mewara 22-Sep-20 8:48am
   
:)
Do have a look at: https://www.codeproject.com/Articles/5279992/Data-Visualization-Insights-with-Matplotlib
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.
   
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

DECLARE  @Output TABLE (
      TypeName NVARCHAR(MAX),
	  Val NVARCHAR(MAX)
	  )
 
      SET @StartIndex = 1
      IF SUBSTRING(@SPInput, LEN(@SPInput) - 1, LEN(@SPInput)) <> @SetSeparator
      BEGIN
            SET @SPInput = @SPInput + @SetSeparator
      END
 
      WHILE CHARINDEX(@SetSeparator, @SPInput) > 0
      BEGIN
            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))
      END

	  SELECT *
	  FROM @Output
   

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