Dynamic Query Without using Dynamic SQL

I am trying to write SQL code that does not make use of dynamic SQL that will search a Table (Data) based upon the search criteria (Search Criteria) of another.

The Search Criteria table contains the following:
Text to Search, Operator, Join
"Shaun", "LIKE", "AND"
"van der Burgt", "=", "AND"
"1980/02/20", "<", "OR"

I am battling to be able to define the "Join" in the statement

	select	*
	from	Data d
			SearchCritera sc
	where	
	(
					case when sc.Operator_Symbol = '=' then sc.Data_Value end = d.Data_Value
			or		case when sc.Operator_Symbol = '<>' then sc.Data_Value end <> d.Data_Value
			or		case when sc.Operator_Symbol = '>=' then sc.Data_Value end >= d.Data_Value
			or		case when sc.Operator_Symbol = '<=' then sc.Data_Value end <= d.Data_Value
			or		case when sc.Operator_Symbol = 'LIKE' then sc.Data_Value end like '%' + d.Data_Value + '%'
	)

Open in new window

Shaun van der BurgtAsked:
Who is Participating?
 
Shaun van der BurgtAuthor Commented:
I managed to solve it by doing the following:

Count how many AND and separately how many OR conditions were in the Search Criteria
Then using a "Having" clause I checked the match Count was Greater and Equal to for separate queries joined by a UNION clause

Through various tests this proves to be the fastest and most efficient way.

Thank you all for your contributions in getting me in the right direction, much appreciated.
2
 
PortletPaulfreelancerCommented:
You have no alternative, it must be dynamic SQL.

(+edit)
Attempting to insert ANY sql command or opeator such as  "LIKE", "AND" , "=", "AND" , "<", "OR" CANNOT be achieved without dynamic SQL. There is literally no alternative.
0
 
Mark WillsTopic AdvisorCommented:
You can certainly do
	select	*
	from	Data d
	cross apply		(select * from SearchCritera) sc
	where	
	(
					case when sc.Operator_Symbol = '=' then sc.Data_Value end = d.Data_Value
			or		case when sc.Operator_Symbol = '<>' then sc.Data_Value end <> d.Data_Value
			or		case when sc.Operator_Symbol = '>=' then sc.Data_Value end >= d.Data_Value
			or		case when sc.Operator_Symbol = '<=' then sc.Data_Value end <= d.Data_Value
			or		case when sc.Operator_Symbol = 'LIKE' then sc.Data_Value end like '%' + d.Data_Value + '%'
	)

Open in new window

and that will work, but to action "JOIN" from searchcritera then you have problems.

you could (maybe) do something like

where 1=1 
AND (case when sc.join = 'AND' then (case when sc.Operator_Symbol = '=' then sc.Data_Value end = d.Data_Value
			or		case when sc.Operator_Symbol = '<>' then sc.Data_Value end <> d.Data_Value
			or		case when sc.Operator_Symbol = '>=' then sc.Data_Value end >= d.Data_Value
			or		case when sc.Operator_Symbol = '<=' then sc.Data_Value end <= d.Data_Value
			or		case when sc.Operator_Symbol = 'LIKE' then sc.Data_Value end like '%' + d.Data_Value + '%') else 1=1 end)
OR (case when sc.join = 'OR' then (case when sc.Operator_Symbol = '=' then sc.Data_Value end = d.Data_Value
			or		case when sc.Operator_Symbol = '<>' then sc.Data_Value end <> d.Data_Value
			or		case when sc.Operator_Symbol = '>=' then sc.Data_Value end >= d.Data_Value
			or		case when sc.Operator_Symbol = '<=' then sc.Data_Value end <= d.Data_Value
			or		case when sc.Operator_Symbol = 'LIKE' then sc.Data_Value end like '%' + d.Data_Value + '%') else 1=1 end)

Open in new window

But very limited with how many operations you can perform - you would need to repeat for how ever many "JOIN" operations there are, so needs a counter. That makes it pretty much unworkable and the reason why Dynamic SQL exists. Unless to cannot / dont want to run as a SP - could still use a function I guess, but needs more thought regardless.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
PortletPaulfreelancerCommented:
Yes, you can build equivalents, but my (attempted) point was that you cannot "INSERT" commands or operators into a sql query without using dynamic SQL.

e.g. this will fail:

declare @operator as varchar(50)
set @operator = '='

select * from sometable where x @operator y
0
 
Scott PletcherSenior DBACommented:
Your request is confusing, particularly on the "search criteria" table.

What is "sc.Data_Value"?  In particular, is it the same as "Text to Search" or something different?

Are the "AND"/"OR" imbedded in the row "join" conditions or are they just selection conditions?  That is, the data row should only be selected if it matches with the ANDs and ORs.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You do not even need to use CASE:
select	*
from Data d
	cross apply (select * from SearchCritera) sc
where	
	(sc.Operator_Symbol = '=' and sc.Data_Value = d.Data_Value) or	
	(sc.Operator_Symbol = '<>' and sc.Data_Value <> d.Data_Value) or		
	(sc.Operator_Symbol = '>=' and sc.Data_Value >= d.Data_Value) or
	(sc.Operator_Symbol = '<=' and sc.Data_Value <= d.Data_Value) or
	(sc.Operator_Symbol = 'LIKE' and sc.Data_Value like '%' + d.Data_Value + '%')

Open in new window

0
 
Scott PletcherSenior DBACommented:
A single CASE statement is more efficient.

I wrote a custom system that did this, but the performance was not great.  Of course it was still so much faster than us humans could do it, it was still worth it.
0
 
Mark WillsTopic AdvisorCommented:
So, by dropping the 'CASE' and not addressing the "JOIN" part - which was your original question, you have awarded points for NOT addressing your question about "JOIN"

Sorry, but will need to object.

No problems with you accepting your own answer....
0
 
Shaun van der BurgtAuthor Commented:
I’m not sure by what you mean by awarding points? I merely marked a response of dropping the CASE as helpful and then proceeded to post my solution. Is there something I missed?

However, I do believe I have solved my problem with my joins as per my description by using a UNION between my AND and OR queries.
0
 
Mark WillsTopic AdvisorCommented:
Hi Shaun,

There were recent changes to the closing process. Your timing coincides with those changes.

I have no problems you accepting your own solution, that is your right. And will help others looking for a solution.

As I understood your problem, it was how to get "JOIN" in there and the only solution I can see that you chose as helpful, didnt address that.

"I am battling to be able to define the "Join" in the statement"

And yet you do say "Thank you all for your contributions in getting me in the right direction, much appreciated" which indicated to me that there maybe others that may have been helpful.

I dont see too many strategies on how to accommodate "JOIN" in your "statement" rather a few ideas as to how the change the statement (interestingly using cross apply) and not address the "JOIN"

Hence my objection....
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
@Shaun, you don't need to choose one of our comments to be a solution if you found a different way to do it.

@Mark, I'm ok with the objection as there are more valid inputs here. But I don't think your justification is valid. How many times that we provided solutions that weren't what the author expected when opened a question? Our job as Experts is also to provide alternative solutions if we think that will fit better for the author's purpose. Isn't because someone opens a question and asking to provide a solution with cursors that we will provide it if we think that the issue can be solved with a better SELECT statement, right?

Cheers.
0
 
Mark WillsTopic AdvisorCommented:
@Vitor,

I am fine with Shaun selecting his own answer.  Have said as much on a number of occasions, and his answer will provide some guidance for others looking for a solution. No problems.

However, the problem at hand was how to incorporate "JOIN" into his own query. And, helping / educating others isnt a "job".

And just like a person wanting to learn about "how does a cursor work", we need to first address the problem at hand. The cursor does have a role to play in SQL. So let us resolve that first (through education), and by all means suggest an alternative. So long as the problem at hand is addressed in such a way they the "Asker" is receiving a quality of service that EE should always strive to provide.

We are not mind readers, so we must engage with the stated problem as it stands and collaborate with the "Asker" to determine what they are really looking for, and possible strategies or alternatives to help provide a solution.

I guess the difference in approach is a bit like being part of a Private Discussion, choosing not to participate in that discussion, and yet, post opinion for public consumption outside the confines of PD. Not really in the best interest for all involved, but essentially "grand standing" a point of view almost in arrogant defiance of the PD. Your opinion about the validity of my Objection is of no value "out here".

The "best" answer is and always will be the right of the Asker to choose. But it is often the trail of breadcrumbs that helps others in a similar predicament that might lead to their own solution. Hence the importance of PAQ, and why EE keeps Solutions. That is a major benefit for all others as far as I am concerned.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.