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


The Zero to N Parameter Problem (Sql Server 2005 and Up -- Update)


The Zero to N Parameter Problem (Sql Server 2005 and Up -- Update)

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

Group: General Forum Members
Points: 201 Visits: 182
Comments posted to this topic are about the item The Zero to N Parameter Problem (Sql Server 2005 and Up -- Update)



darkhelmutis
darkhelmutis
SSC Veteran
SSC Veteran (262 reputation)SSC Veteran (262 reputation)SSC Veteran (262 reputation)SSC Veteran (262 reputation)SSC Veteran (262 reputation)SSC Veteran (262 reputation)SSC Veteran (262 reputation)SSC Veteran (262 reputation)

Group: General Forum Members
Points: 262 Visits: 880
Interesting idea to use XML as a means of passing the parameters, could certainly be very practical.

I am interested in the blanket statement: 'dynamic SQL is a bad idea'? Yes, if handled improperly there is the possibility of a SQL injection attack. However, this is mitigated with the use of the parameter list. The biggest obstacles I have found to deal with depends on the number of applicable predicate conditions. If the list is short, say less than or equal to ten conditions, then code maintenance isn't that bad. As that list increases, I will admit that it becomes a hassle to maintain.

Erland Sommarskog provides probably the best documentation on how to handle dynamic search conditions. He provides multiple solutions, evaluating the pro's and con's of each:

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

And then choose to view the document based on the version of SQL Server that fits your situation.
sholliday
sholliday
SSC Veteran
SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)

Group: General Forum Members
Points: 201 Visits: 182
My comment "dynamic SQL isn't a good idea" is directly related to injection attacks, as you state.

What I like about this solution is that everything (client side with a strong dataset, and this procedure on the server side) is "strongly typed" and isn't alot of string concatentation.

Client side, I would write something like

ParameterDS ds = new ParameterDS();

ds.ScalerRow srow = ds.NewScalarRow();
srow.FromDate = DateTime.Now;
srom.ToDate = DateTime.Now;

ds.Scalar.AddScalarRow(srow);

something like that.

So I get strong typing.

It's not the only way to skin a cat, but it's my preferred and still serves me well after 12 or so years.

Thanks for the feedback.



sholliday
sholliday
SSC Veteran
SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)

Group: General Forum Members
Points: 201 Visits: 182
Note, this article is an update from a previous (pre-Sql Server 2005) version, here:

http://www.sqlservercentral.com/articles/Stored+Procedures/thezerotonparameterproblem/2283/



yariv 49193
yariv 49193
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 14
This is very nice,
but there's a better solution for the same issue.

http://www.madeira.co.il/fully-dynamic-search-conditions-vs-sql-injection
gary.strange-sqlconsumer
gary.strange-sqlconsumer
SSC-Enthusiastic
SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)

Group: General Forum Members
Points: 195 Visits: 613
Thanks for the article,

Consider using strongly typed xml and discarding fuzzy checking like datalength > 10

So you're SP's input parameter might be something like @parmeters XML (DOCUMENT dbo.MyXSD)

http://technet.microsoft.com/en-us/library/ms184277.aspx

You may also consider user defined table type (UDTT) parameters as table valued parameters (TVP), if your calling code is already manipulating datasets.

http://technet.microsoft.com/en-us/library/bb510489(v=sql.105).aspx
Mike DiRenzo
Mike DiRenzo
SSC Veteran
SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)

Group: General Forum Members
Points: 269 Visits: 210
I always get a warm and fuzzy feeling when I see XML and TSQL so kudos for your solution. Since you are using XML, you can also pass in multi-hierarchy documents and or document fragments - which can give you more power. Please note the table and table valued parameters comment someone made and even the XSD idea can probably be perceived as superior alternatives. I wonder though, does the sproc scale - given the fact that there may not always be the same parameters coming in. I ask because I came across a situation recently whereby a sproc had overloaded input parameters - not every time would all the parameters be set. This caused the execution plan to be somewhat less than optimal.

Good article and thanks for sharing!
sneumersky
sneumersky
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2486 Visits: 487
Not a bad tool to have when flexibility trumps all other design objectives. Thank you for taking the time to contribute. :-)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85450 Visits: 41080
I appreciate every article because it is an exchange of ideas and it does take an author time to put together. I would like to suggest a couple of improvements, though. Some of the suggestions have already been mentioned but I've included my thoughts on them in the following for completeness. I hope you take them as helpful rather than any form of personal attack.

The NorthWind database is an SQL Server 2000 sample database. Since the title of your article clearly states "Sql {sic}Server 2005 and Up --Update", I strongly recommend that you modify your examples to work with the newer sample databases available as of SQL Server 2005.

In that same vein, I also recommend that you update the comments in the code because "Query Analyzer" does not exist in "SQL Server 2005 and Up".

The code is setup to shred some XML but the parameters in the example usage of the stored procedure are not in XML form in the article. That may be more the fault of the tool used to submit the article but a posted correction on your part would be mighty handy because those examples would show the necessary form of the parameters (example usage) for the stored procedure to work.

As someone else mentioned, I strongly oppose making blanket statements such as the following from the article.
A comma delimited list just does not seem to cut it anymore, and dynamic SQL isn't a good idea.


If you want to know what a good idea and just how injection proof dynamic SQL can actually be, please see the following article.
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Then, to follow up such blanket statements with the following...

This method is not a "cure all" method. There is a performance penalty for using this method. If you have a procedure where performance is the main goal, and you have a lot of records to go through, this solution may not be the answer.
...{snip}...
Good luck. The method is useful ~at times. Please be aware of possible scalability issues, since there is a performance hit by making the procedure so flexible.
...{snip}...
Please heed my warning about scalability, and do not consider this a cure-all.


... seems to indicate that maybe the methods that you summarily dismissed in a blanket fashion might not be so bad after all. You also didn't mention that passing table valued parameters is also a highly effective method that avoids the pain of shredding XML nor any possibilities of what else one might use if the code does, in fact, produce scalability issues.

The following statement is also a bit misleading to the uninitiated who may be looking for a solution to the "0 to N" parameter problem...


...or said another way, I want to specify N parameters, where N is 1 through infinity.


Although the XML data-type can certainly accommodate a rather large number of parameters, its limited to 2GB and "infinity" isn't actually possible. Although I thought the related application was poorly designed, I have had to accommodate the passing of more than 2GB of parameters from an application and a single XML variable won't cut it despite the claim of "infinity". Further, passing a table variable parameter might, in fact, allow one to exceed that 2GB limit (as crazy as that may be).

You also wrote…

I learned this method from a previous colleague. The example is an original one coded against the Northwind database.


To add some additional credibility to your article, I recommend that you review the code and apply some common best practices such as using the 2 part naming convention, using table aliases on all column references where more than one table is referenced by a statement, using consistent casing, avoiding multiple statements (DECLARE) on one line, and maybe even taking the time to consider reducing the line length to prevent the necessity of have to scroll-right to see the end of a line and to facilitate easier reading.

You might also want to "modernize" some of the techniques. For example, doing a SELECT COUNT(*)to figure out the row counts of the table variables that you just INSERTed into could be replaced by the much more efficient SELECT @CounterVariable = @@ROWCOUNT.

And consider getting rid of all the empty "/* */" in the code. At best, they're annoying and the proper use of white space would make the code more pleasurable to read.

This article could be a fantastic article but it needs some tweaking.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
sholliday
sholliday
SSC Veteran
SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)

Group: General Forum Members
Points: 201 Visits: 182
gary.strange-sqlconsumer (12/16/2013)
Thanks for the article,

Consider using strongly typed xml and discarding fuzzy checking like datalength > 10

So you're SP's input parameter might be something like @parmeters XML (DOCUMENT dbo.MyXSD)

http://technet.microsoft.com/en-us/library/ms184277.aspx

You may also consider user defined table type (UDTT) parameters as table valued parameters (TVP), if your calling code is already manipulating datasets.

http://technet.microsoft.com/en-us/library/bb510489(v=sql.105).aspx



If the parameters are more often .. different than the same.......then consider the

OPTION(RECOMPILE)

hint.



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