Conditional Joins and Selection Criteria in a Stored Proc? 1

[hoialmen]

Greetings All!

I am working on a Stored Proc for a ColdFusion development team and am running into a few issues that I was wondering if anyone could help me with.

The SQL being passed to the server by the ColdFusion pages is currently being generated dynamically on the client side. The development team has recently determined that converting the SQL to a Stored Procs and running them on the server side would improve the performance of their app (and I agree). The issues I am running into is that their app is sending dynamic SQL based on the info that the user is requesting. This works fine on the application side but the coding standards implemented by our DBA group don't permit the use of dynamic SQL. So, I'm wondering if there is a SQL expert out there that may have some ideas on how I can get around this without writing billions of Stored Procs to handle each variation in the requested data.

Here are some examples of what I am up against:

(
@Var1 varchar(50),
@Var2 int
)

SELECT Tbl1.Col1, Tbl1.Col2, Tbl1.Col3
FROM Table1 AS Tbl1
<cfif ("local.Var1") = "Test"> -- ColdFusion IF Tag
INNER JOIN Table2 AS Tbl2 ON Tbl1.Col1 = Tbl2.Col1
</cfif> -- ColdFusion END IF Tag
WHERE Tbl1.Col3 = 'Nuts'
<cfif ("local.Var1") = "Test"> -- ColdFusion IF Tag
OR Tbl1.Col3 = 'Fruits'
</cfif>> -- ColdFusion END IF Tag

So, basically what this code is doing is writing a SQL statement to the server and, if Var1 = 'Test', it is adding 2 lines of SQL - 1 JOIN and 1 OR statement. Of course the real thing is quite a bit more complex than this but hopefully you get the idea. I come from the world of VB and I know you can add an IIF (immediate IF) statement to the selection criteria to dynamically change it at runtime. Does TSQL have an equivalent that could possibly be used here? Or maybe I simply need to take the blinders off and rethink my approach?

Thanks in advance for your thoughts!

Nathan C. Hoialmen
President
Encephalon Business Solutions, LLC

http://www.encephalon.net

 

[tlbroadbent]

It is fairly easy to use the variable in the Where criteria.

SELECT Tbl1.Col1, Tbl1.Col2, Tbl1.Col3
FROM Table1 AS Tbl1
WHERE Tbl1.Col3 = 'Nuts'
   OR (@var1='test' and Tbl1.Col3 = 'Fruits')

Modifying the query structure based on a parameter is a slightly more difficult but still doable.

If @var1 = 'test'
  Begin
    SELECT Tbl1.Col1, Tbl1.Col2, Tbl1.Col3
    FROM Table1 AS Tbl1
    INNER JOIN Table2 AS Tbl2 ON Tbl1.Col1 = Tbl2.Col1
    WHERE Tbl1.Col3 = 'Nuts'
       OR Tbl1.Col3 = 'Fruits'
  End
Else
  Begin
    SELECT Tbl1.Col1, Tbl1.Col2, Tbl1.Col3
    FROM Table1 AS Tbl1
    WHERE Tbl1.Col3 = 'Nuts'
  End

Does this help?


If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.comcast.net/sql/sql_articles.htm

 

[vongrunt]

You can translate INNER JOINs into EXISTS() statements:

SELECT Tbl1.Col1, Tbl1.Col2, Tbl1.Col3
FROM Table1 AS Tbl1
WHERE (Tbl1.Col3 = 'Nuts' OR (@var1='test' and Tbl1.Col3 = 'Fruits'))
AND (@var<>'test' OR EXISTS(select * from Table2 AS Tbl2 WHERE Tbl1.Col1=Tbl2.Col1))

SQL Server apparently works quite well with constant expressions and EXISTS(), so performance penalty may not be (too) high. Some other tricks include CASE/END, COALESCE(), ISNULL(), Boolean implication etc.

Still, personally I'd go old-fashioned way (IF this ELSE that) wherever possible.

 

[tlbroadbent]

If Exists will work if you only want to select rows in tbl1 that have a related row in tbl2 but will not work if you need to select data from tbl2.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.comcast.net/sql/sql_articles.htm

 

[hoialmen]

Terry,

You're a Rock Star! Your logic works well for this simple example. I hope I can use it in my MUCH more complex scenario. The problem I am dealing with is that I currently have 5 conditional JOINS and 4 conditional selection criteria. This could complicate things a bit because there are numerous combinations of the two to consider. But, at least this will get me going in the right direction.

Thanks!

Nathan C. Hoialmen
President
Encephalon Business Solutions, LLC

http://www.encephalon.net

 

[vongrunt]

If Exists will work if you only want to select rows in tbl1 that have a related row in tbl2 but will not work if you need to select data from tbl2.

True... if you eventually need data from Tbl2 use LEFT JOIN instead:

SELECT ...
FROM Table1 AS Tbl1
LEFT JOIN Table2 AS Tbl2 ON Tbl1.Col1=Tbl2.Col1
WHERE (Tbl1.Col3 = 'Nuts' OR (@var1='test' and Tbl1.Col3 = 'Fruits'))
AND (@var1<>'test' OR Tbl2.Col1 IS NOT NULL)

This of course assumes that tables are joined 1:1.

 

[donutman]

Nathan,
From your two threads, it's becoming clear that the number of SPs could get out of hand. It's not clear just how many variations you anticipate.
But until we know more, I am willing to say this, IMHO 20 SPs that are similar but different does not make for a bad solution. It may be tedious to write them and it may look inelegant (leading many programmers to think dynamic SQL), but performance is king in my book...not aesthetics, elegance or disk space.
The one concern I have about using a conditional branch in a SP as Terry has suggested is whether or not the SP can be optimized properly. Because I don't know, I've always preferred to play it safe and write two whenever performance is important.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]