TSQL LAB 2 - Writing Conditional WHERE Clauses

Question

TSQL LAB 2 - Writing Conditional WHERE Clauses

Viewing 15 posts - 91 through 105 (of 106 total)

You must be logged in to reply to this topic. Login to reply

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 1

What prevents dynamic SQL from either (A) making use of an existing inline table valued function, or (B) dynamically creating a subquery equivalent to the inline table valued function and APPLYing it??

Not to take away from all the discussion about COALESCE, etc., but previous forums have pretty well established the benefits of parameterized dynamic SQL against old-school approaches to building "universal" WHERE clauses.

That said, I would be very interested in seeing any studies and statistics to the contrary.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

chisholmd SSCrazy Points: 2490 More actions · Answer 2

Not to be too lazy but do you have links to those other forum posts?

Dave

Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors)

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

I haven't saved them, no. Some might be a year or more older. I'll see if I can search for a couple.

--------

Edited:

You can find tens of thousands if you search on DYNAMIC WHERE / CONDITIONAL WHERE etc.

Try these for starters;

http://www.sommarskog.se/dyn-search-2008.html

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]

http://www.sqlskills.com/BLOGS/KIMBERLY/post/EXEC-and-sp_executesql-how-are-they-different.aspx

The last is included for fairness. Read on down into the discussion and you will see Kimberly allude to issues with sp_executeSQL.

I will listen respectfully to whatever she has to say.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

sql-troubles SSC Journeyman Points: 95 More actions · Answer 4

Of course a dynamic script can call an inline table-valued function, though, as far I know, a dynamic script can't be run from an inline table-valued function because neither EXEC nor sp_executesql could be called from a SQL Server UDF of any type.

There is also a third possibility, namely to create a CLR UDF or stored procedure, following to built the SQL Script inside of it based on the provided parameters. CLR objects come with their own limitations though they could prove to be quite useful in certain scenarios, for example in running a dynamically created script for each record in a dataset like in this post (http://sql-troubles.blogspot.com/2010/06/number-of-records-clr-version.html).

Also for me it would be interesting to see comparisons between the 2 or 3 approaches, especially when they consider a whole range of possibilities of executing the same object but with different parameters.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 5

though, as far I know, a dynamic script can't be run from an inline table-valued function because neither EXEC nor sp_executesql could be called from a SQL Server UDF of any type.

That's correct. (Why would you want to?) For clarity's sake, please refrain from referring to inline table-valued functions as UDFs. They are not the same thing. The performance of inline table valued functions is WAY better than UDFs. You may know the difference but don't confuse newcomers.

There is also a third possibility, namely to create a CLR UDF or stored procedure, following to built the SQL Script inside of it based on the provided parameters. CLR objects come with their own limitations though they could prove to be quite useful in certain scenarios, for example in running a dynamically created script for each record in a dataset like in this post (http://sql-troubles.blogspot.com/2010/06/number-of-records-clr-version.html).

While CLRs have many advantages over T-SQL in character manipulation, I thought this post was concerned with dynamically selecting rows. That is better accomplished in T-SQL where the optimizer can work out an efficient plan to build the result set. One of the major problems with trying to build universal WHERE clauses and passing nulls is that the optimizer often can't determine the appropriate index to use and chooses to do a table scan.

(If you were talking about using CLR to dynamically generate TSQL code to be executed, never mind.)

I've written code that creates functions and stored procedures on the fly and then uses or executes them. However I have since abandoned that approach because, in my experience, parameterized dynamic SQL has proven to be simpler and more efficient.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

sql-troubles SSC Journeyman Points: 95 More actions · Answer 6

Sorry, I don't want to enter in any conflicts related to terminology (or of any type) as meaning is highly dependent on context, communities and is in continuous change. It's also possible that I have misunderstood the denominations used and for this I apologize, though as per SQL Server Books Online (see CREATE FUNCTION http://msdn.microsoft.com/en-us/library/ms186755.aspx) as UDF (used defined function) are considered scalar functions, inline table-valued functions, multistatement table-valued functions, CLR scalar functions and CLR table-valued functions. Also the documentation could be wrong, though I prefer to stick to it and take into account possible alternative denominations.

Each person, no matter of its intellectual or skills development has its own pace of learning, I understand that too much (contradictory) information could create confusion, though I believe that it's better to know that something exists even if it's still hard to apprehend that something. When belief comes into play there is already terrain for conflict. I hope you respect my opinion as I respect your opinion too.

Best regards

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 7

sql-troubles (7/30/2010)
Sorry, I don't want to enter in any conflicts related to terminology (or of any type) as meaning is highly dependent on context, communities and is in continuous change. It's also possible that I have misunderstood the denominations used and for this I apologize, though as per SQL Server Books Online (see CREATE FUNCTION http://msdn.microsoft.com/en-us/library/ms186755.aspx) as UDF (used defined function) are considered scalar functions, inline table-valued functions, multistatement table-valued functions, CLR scalar functions and CLR table-valued functions. Also the documentation could be wrong, though I prefer to stick to it and take into account possible alternative denominations.
Each person, no matter of its intellectual or skills development has its own pace of learning, I understand that too much (contradictory) information could create confusion, though I believe that it's better to know that something exists even if it's still hard to apprehend that something. When belief comes into play there is already terrain for conflict. I hope you respect my opinion as I respect your opinion too.
Best regards

I must yield to your authority regarding the meaning of UDF. I stand corrected and will try to be more clear in my terminology. 🙂

I am in the habit of using UDF to mean a scalar function. No disrespect was intended.

Lets try this again with respect to functions. My summary, without reference to outside authority.

Scalar user-defined functions: classic way of doing functions, slow performing compared to inline table valued functions or CLR functions, potentially fatal if they do SELECTs from the database themselves.

Multi-statement table-valued functions: handy for logic that would be excessively complicated to do with case statements, but suffer from the same performance problems as scalar functions.

Inline table valued functions: essentially parameterized views which can be accurately evaluated and worked into an efficient execution plan by the optimizer

CLR functions: can provide performance gains in sophisticated character manipulations or calculations (and much much more... just ask Paul White)

Best regards.

Bob

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

sql-troubles SSC Journeyman Points: 95 More actions · Answer 8

Thank you Bob, we have similar perception on the "definition" of each type of function.

Best regards,

Adrian

roman.asadovsky SSC Enthusiast Points: 140 More actions · Answer 9

Jacob,

there is a simple solution to make < and > work with non-dynamic version, when parameter is null.

In your example:

/*

Option 1

This will not work if "@ReorderPoint" IS NULL

*/

SELECT *

FROM Production.Product

WHERE ReorderPoint > ISNULL(@ReorderPoint,ReorderPoint)

make it like this:

-- This does work if "@ReorderPoint" IS NULL

SELECT *

FROM Production.Product

WHERE ReorderPoint > ISNULL(@ReorderPoint, ReorderPoint -1)

roman.asadovsky SSC Enthusiast Points: 140 More actions · Answer 10

There is a great article by the way, on dynamic SQL and the ways to avoid it here:

http://www.sommarskog.se/dynamic_sql.html

SQLRNNR SSC Guru Points: 281334 More actions · Answer 11

Thanks - nice article.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 12

Ok... after reading this a second time, I have to ask... what about performance? Under optimal conditions (ie. correct indexes on the table), which method(s) will actually be the most performant and still be SQL Injection proof? That's the kind of information that should also be included in articles of this nature. And, no... I really don't want to have to go to someone else's article to find out that kind of information. A standard million row test should do. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

forjonathanwilson SSC Enthusiast Points: 139 More actions · Answer 13

I didnt notice if anyone mentioned this, but I've run into some very odd things when attempting to pass NULL as a parameter value though a web service back to SQL server. We have standardized on passing a 0 back when we want to exclude that parameter and the conditional where clause looks something like:

select *

from someTable

where

(someTable.Value = @myParam OR @myParam = 0)

sql-troubles SSC Journeyman Points: 95 More actions · Answer 14

This might work, now it depends what meaning the 0 value has for you:

select *

from someTable

where IsNull(someTable.Value, 0) = @myParam

forjonathanwilson SSC Enthusiast Points: 139 More actions · Answer 15

that will only get the records where the value is null. and only if you passa zero as the param value.