SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The Zero to N Parameter Problem


The Zero to N Parameter Problem

Author
Message
sholliday
sholliday
SSC Veteran
SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)

Group: General Forum Members
Points: 205 Visits: 183
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sholliday/thezerotonparameterproblem.asp



sholliday
sholliday
SSC Veteran
SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)

Group: General Forum Members
Points: 205 Visits: 183

Just to follow up, if you do a google search for:

"How to Optimize the Use of the OR Clause When Used with Parameters"

This guy talks about the OR issue....which is related to the Zero to N solution I propose.

Again, it is not a "cure all", it has to be weighed for less complexity vs lost performance.

..





Duray AKAR
Duray AKAR
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 1

Looks cool, definitey useful in many report scenarios.

I just 1 question...

Is this approach SQL injection safe?

I did not see any issues with your specific query at first sight, but I get a little worried when I see untyped parameters being passed, or dynamic queries being generated. I did not have enough time to make a thorough analysis here, I assume you could answer that, so it is easier for me to ask than spend the time

If not, a little warning might be good to accompany the article.

Thank you.

Duray AKAR


Ian Yates
Ian Yates
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1884 Visits: 445

I see this as being a LOT more work to implement, test and debug than simply accepting a number of parameters that have default values for them? The XML allows you to specify a number of orderIDs - again, accept a varchar(8000) param as a CSV list of order IDs and use a simple UDF to split them into a table against which you can join... The default values for all parameters can be NULL - if it is NULL then it is ignored (by appropriate code in the where clause). Apart from that, I tend to have followed the same approach for many of the filtering screens in our application.

An optimisation can be made if you know that certain parameters are likely to appear by coding different branches of code in the stored proc so that the where clauses in the statements can make use of indices (no OR statements on the parameters that are always used in a particular branch).

I haven't done any performance comparison of CSV string splitting VS XML, but I imagine the overhead of invoking the XML processor, as well as the annoyance of building the XML client-side (or even worse, testing in Query Analyser) means I'll be sticking to CSV lists (where needed) and default values of NULL for parameters unless convinced otherwise...

I will be happily proven wrong - so long as it's done nicely





Mike Dougherty
Mike Dougherty
SSC-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 Visits: 952
I don't see this technique as an improvement where people are already using the CSV string technique. By the time you have serialized an arbitrarily large number of unrelated parameters in strings, the XML would bring to the table a standard format. (after all, XML is essentially a string solution too)
Since table variables cannot be passed between stored procedures, the XML document is a good way to serialize a resultset to use as input to another another procedure. When simpler strategies fail, this technique may be one more option. It is a good one for the SQL bag of tricks.
Antares686
Antares686
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: Moderators
Points: 11754 Visits: 780
After years of trying I prefer to perform a loop in my application to pass the data. It is safer, syntax issues are fewer and it performs at about the same speed. The big gain is that I can decide how to handle each and every error independantly without all the extra overhead. Programming laguages such as C++,C#,VB and all have better methods for sperating a data set than you can get inside SQL code. And if you are concenred with treating as a batch transaction versus independant transactions just execute "BEGIN TRANSACTION" over the connection first and end with COMMIT or ROLLBACK as you need. As well you should always keep your code simple where it is really a lot of work to trouble shoot sometimes the simplest of issues with this method and you do open yourself to injection attacks.



joshcsmith13
joshcsmith13
SSChasing Mays
SSChasing Mays (619 reputation)SSChasing Mays (619 reputation)SSChasing Mays (619 reputation)SSChasing Mays (619 reputation)SSChasing Mays (619 reputation)SSChasing Mays (619 reputation)SSChasing Mays (619 reputation)SSChasing Mays (619 reputation)

Group: General Forum Members
Points: 619 Visits: 254
I guess I misinterpreted what this article was going to explain. I was thinking it would be a cool way to specify a variable number of parameters of different types. The example SP is still hard-coded to only filter on a few specified fields. As someone else said, I will have this in my back pocket should a need for it arise, but in the mean time I don't see a vast improvement over delimited text parameters.




noeld
noeld
SSCrazy Eights
SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)

Group: General Forum Members
Points: 9810 Visits: 2048

Just to make something clear:

if the type is always the sam SQL Server 2000 can handle 2100

And to be honest, if you need more than that there are probably bigger issues with you design ;-)

if it is all abou list processing then csv are way nicer (in my opinion)

The only case I have used something like this is when multiple (master/detail) records needed to be transfered in on go. But for reporting I think this is not the case.

Just my $0.02




* Noel
carie dobson
carie dobson
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 39

That is definitely a cool way to skin the cat. I have been fascinated by XML since the early days and still am.

There have been plenty of times I have wanted to use a single proc to pull back the same columns but with different criteria. The following is a method I came up with a couple of years ago - has been quite handy. I actually almost had it published in Sql Svr mag but the editor had concerns over performance (so they decided not to use it), so use it w/ a grain of salt. In my personal experience, performance has not been an issue and the gains from not using a bunch of "IF" statements, dynamic SQL, or maintaining multiple procs that return the same results has been a winner in my book.

CREATE PROC ProcName

@Param1 varchar(50) = null,

@Param2 int = null

AS

SELECT field1, field2

FROM tablename

WHERE field1 = isnull(@Param1, field1)

AND field2 = isnull(@Param2, field2)

Summary: If you pass the parameter, it's used for the comparison. If not, the field is compared against itself. Very simple, very easy to use.


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search