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-Addicted
SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)

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



sholliday
sholliday
SSC-Addicted
SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)

Group: General Forum Members
Points: 449 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 Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 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
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4364 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
Old Hand
Old Hand (310 reputation)Old Hand (310 reputation)Old Hand (310 reputation)Old Hand (310 reputation)Old Hand (310 reputation)Old Hand (310 reputation)Old Hand (310 reputation)Old Hand (310 reputation)

Group: General Forum Members
Points: 310 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
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: Moderators
Points: 25352 Visits: 785
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
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1023 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
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21522 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
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 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