Xml data as the parameter in a stored procedure for multiple purposes

Question

Xml data as the parameter in a stored procedure for multiple purposes

seaport

SSCrazy

Points: 2491
More actions
August 15, 2008 at 3:13 pm

#195911

I am new to XML but amazed by its great potential. Now I am using it in create a stored procedure that covers a dozen of procedures that I created before.
For example, I have a table called Orders and several related tables (like OrderInsurance). The OrderID is the primary key of Orders table, as well as in other tables. Previously, I got stored procedures like
prOrder_Update, prOrder_Delete, prOrder_load, prOrderInsurance_Update, prOrderInsurance_Delete, etc.
Now I just need one procedure prOrder, which has the following params:
@OrderID
@Action (value could be 'Order_Update', 'Order_Load', 'Order_Delete', etc)
@params as xml
Inside the procedure, there is one set of codes for each @Action. All needed parameters for one action is inside the xml parameter, which can be parsed into one or multiple data sets.
This method makes the management of procedures much more easier. I am not seeing any downside yet. I do understand that this may make the SQL Server confusing in creating the execution plan and lead to low performance. But the purpose of this kind of procedure is to load or update one or several records, rather than reporting on large amount of rows. So the performance is not a issue here.
I wonder whether other people are doing the same thing, or any caution I should take on this approach.
Any feedback is appreciated.
Thanks.
Seaport

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184388 More actions · Answer 1

This is the power of XML in that it does allow you some flexibility, but with flexibility many times comes lack of readability and lower performance, as you note. Here are the issues I would see with the approach you are taking:

1. Performance hit. Sure you are only affecting 1 or a few rows, but, how often are you doing it? A reporting procedure selecting thousands of rows over a couple of seconds run once a day is, IMO, a lesser candidate for optimization than an insert/update/delete procedure affecting few rows, that is being run thousands of times a day. Which one is affecting the system more?

2. Readability. You are new to XML, but what about the developer that follows you? Will that person or persons be able to understand and debug your code?

3. Maintainability. How easy or difficult will it be to add/remove/update logic in the procedure?

My first job I worked with a brilliant developer, but by emphasizing flexibility his code was nearly impossible to read, understand, maintain, and modify, and did not perform as well as simpler code.

Just my 2 cents.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

seaport SSCrazy Points: 2491 More actions · Answer 2

Hi, Jack,

I completely agree with you.

As I understand, stored procedures are used by administrators/developers to set permissions. So, another big problem, in addition to what you said, is that there is no way to set permission when a stored procedure is created to do too many things.

Thanks.

Seaport

Timothy J Hartford SSCarpal Tunnel Points: 4214 More actions · Answer 3

My 2 cents: There is an application and there is a database. There are a lot of reasons that the line can get blurry, but you need to be sure to define the line in your environment. Where are the strengths in your environment? Database development, or code development? This specific solution seems like a great way to move the own-ness of updates from the application developer to the db-developer, but IMO a properly designed data access layer will provide better error handling, better performance, easier maintainability, and easier readability.