Replace Stored Procedure Parameters with a Single XML Parameter – BAD!
I had a couple readers write in with a question specific to passing XML to a stored procedure…and item I briefly mentioned in the Editorial earlier this week.
Maurice
I was eagerly waiting the comments you had on the negative impact of passing to stored proc. many parameters using XML fragments. This is what you told in your previous comment.
I found that useful in some case and I didn’t ran into problem. It could be valuable if you could share your thought on that matter.
Thomas
I have inherited a system that runs exactly as you described – 1 xml param in all procs.
Please feel free to discuss more of your issues around that. In my case it even went as far as handing UI layout data back and forth via the XML – not just the immediate proc results needed.
Its a super bad design.
The intention of this series this week is to prompt you to think about using XML without specification (no defining XSD) as a less than optimal practice, because there is no required Contract for the data contained within the XML.
One example of this kind of abuse was a system I worked with designed to simply send XML as input to every stored procedure. What I mean by that is this…the ONLY input parameter to every stored procedure was a single XML document with no XSD documentation. Therefore, there was no contract that could be assured between the stored procedure and the calling program. It resulted in software that could work incorrectly without raising real errors.
For example, consider a web site where you have a person and their address. Later, because you decide to modify your address data structure for some reason, you first modify the web site to generate different XML to pass the required parameters. The problem is that, unless the stored procedure is also modified to extract out the additional data from the XML variable, it doesn’t know what to do with it, and simply drops it on the floor. Conversely, it was possible to have local stored procedure variables resulting in default values if the necessary tags were no longer passed in the XML fragment from the calling program.
In short, both the client and the server (stored procedure) had to be modified independently without an XSD to enforce how the communication is to be performed resulting in potential error where better development practices would make this a non-issue.
I think this discussion should turn to the discussion of XML in the database. Is it really valuable, and if so, why? If you’d like to comment on the use of un-documented XML or the role of XML in a relational database, feel free to share your opinion or experience by writing in to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured White Paper(s)
How to Use SQL Server’s Extended Events and Notifications to Pro actively Resolve Performance Issues
read more)