Post reply

xml as input parameter in stored procedure

  • December 7, 2012 at 10:43 am

    #285974

    Hi,

    I have been using xml data type as an input parameter but never thought about why we need to use xml.

    One of my friend who is learning sql server asked me this question.

    Any thoughts?

    Thanks in advance.

  • December 7, 2012 at 10:59 am

    #1566707

    Only two thoughts...

    1.) Why does it matter? For any given stored procedure, there's an objective that it needs to accomplish. You only need input in the xml format if that's what the procedure needs to actually do - as in you have to process the xml using SQL as opposed to object-oriented VBScript, and only the SQL processing can give you the desired output.

    2.) When you need help, you need to be a lot more specific about what your objective is, or the help you get, if any, might not be very useful. You've given no details, but seek the merits of the use of xml without any specific application of it to talk about. In general, unless you have a specific requirement to process xml using SQL, there's no particular benefit to using it for all parameters, as that just makes your procedure do more work to decode the xml with no particular benefit.

    Does that help?

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • December 7, 2012 at 1:55 pm

    #1566759

    I've designed stored procedures with XML parameters before to allow the calling application to insert or update multiple records at the same time with just one stored proc call. This is usefull in master/detail type forms, or forms with editable grids.

    It could also be used to pass in a list of criteria into a stored procedure, e.g. "give me all year 2012 sales for the states of CA, WA, OR", the states could be put into a simple XML structure.

  • December 7, 2012 at 1:57 pm

    #1566761

    Thanks.

  • December 7, 2012 at 2:39 pm

    #1566777

    The problem with XML for such "simple" structures is that they're extremely bloated with totally unnecessary tag information. While it doesn't seem bad on a single call to a stored procedure, multiply that times the number of hits that a proc may take and see why things like IO suddenly becomes a problem. Multiply that by 10 or 20 stored procs all being hit the same way and see why the pipe fills up so quickly. Yep... you can buy hardware to handle that. But there's nothing like a 200 to 1,600% drop in IO without having to give up any functionality.

    --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.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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