Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

The Zero to N Parameter Problem Expand / Collapse
Author
Message
Posted Wednesday, February 8, 2006 4:56 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 4, 2014 7:31 AM
Points: 117, Visits: 176
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sholliday/thezerotonparameterproblem.asp


Post #256916
Posted Friday, February 24, 2006 2:48 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 4, 2014 7:31 AM
Points: 117, Visits: 176

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.

 

..




Post #261460
Posted Tuesday, February 28, 2006 4:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 30, 2006 11:06 AM
Points: 11, 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

Post #261890
Posted Tuesday, February 28, 2006 5:19 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 30, 2014 10:08 PM
Points: 1,038, Visits: 444

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




Post #261914
Posted Tuesday, February 28, 2006 6:31 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 22, 2010 8:59 AM
Points: 110, 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.
Post #261929
Posted Tuesday, February 28, 2006 7:13 AM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: Tuesday, April 22, 2014 1:22 PM
Points: 8,369, Visits: 734
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.


Post #261942
Posted Tuesday, February 28, 2006 9:54 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 4:09 PM
Points: 506, Visits: 237
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.
 



Post #262021
Posted Wednesday, March 1, 2006 9:12 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, July 20, 2014 11:55 AM
Points: 1,945, Visits: 2,860

I already can have 1000+ parameters in T-SQL Stored Procedures.  I just need to ignore the ones that default to NULL and use some simple code with COALESCE to get the effect of a varying parameter list.  No XML, no CSV parsing (which can be done without loops, BTW), no proprietary code. 

 



Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #262373
Posted Wednesday, March 1, 2006 12:44 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 5:51 AM
Points: 6,266, Visits: 2,028

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
Post #262466
Posted Wednesday, February 28, 2007 10:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 8, 2011 8:07 AM
Points: 26, 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.

Post #348214
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse