Dynamic query with dynamic records in SQL

Question

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

SQL

@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 7-Sep-20 20:27pm

Vishal0903

Updated 7-Sep-20 23:02pm

Sandeep Mewara

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Answer 1 · 2020-09-07T21:46:00

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:

SQL

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:

C#

//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[^]

OriginalGriff · Answer 2 · 2020-09-07T21:47:00

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.

Siddharth Sreelal · Answer 3 · 2020-09-07T23:03:00

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.

SQL

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

Dynamic query with dynamic records in SQL

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0