SQL Server Performance

Passing Arrays to SP

Discussion in 'General Developer Questions' started by Raulie, Jan 30, 2004.

  1. Raulie New Member

    Whats the best method to pass an array to an SP using ASP. For instance if you have an order and want to add multiple orderdetails to the order. Something calling the SP multiple times in one batch.
  2. Negative New Member

    You have several options available. The few that I can think of off the top of my head are passing in a delimited string, passing in XML, and using optional parameters. I am sure there are more, but that is all I can think of at the moment.

    A delimited string is pretty easy, search sqlteam.com for a simple function to return the string as a table variable. For XML, look up OPENXML in BOL. Optional parameters are just parameters that have a default value specified, however they will only work in certain instances.

    The other option is to just handle the different parts in your code. For example you would call a stored procedure to insert the order, then in a loop call a stored procedure to insert the order details.
  3. vbkenya New Member

    Just a thought here:

    I am assuming that you've thought about transactional issues while evaluating the various options available.

    Passing the whole array in the form of an XML dataset or a delimited string might imply that all the order details must be committed together or not at all. Looping through each order detail within client code may indicate the intention of atomic independence betweeen the items. There is a risk that not all order details will be committed if anything happens.

    Nathan H.O.
  4. Raulie New Member

    We dont use XML here. I looked at the code at SQL Team are you refering to the sp_ParseArray?
  5. Raulie New Member

    Create procedure sp_ParseArray
    ( @Array varchar(1000),
    @separator char(1) )
    -- Created by [email protected]
    set nocount on
    -- @Array is the array we wish to parse
    -- @Separator is the separator charactor such as a comma
    declare @separator_position int -- This is used to locate each separator character
    declare @array_value varchar(1000) -- this holds each array value as it is returned

    -- For my loop to work I need an extra separator at the end. I always look to the
    -- left of the separator character for each array value
    set @array = @array + @separator

    -- Loop through the string searching for separtor characters
    while patindex('%' + @separator + '%' , @array) <> 0

    -- patindex matches the a pattern against a string
    select @separator_position = patindex('%' + @separator + '%' , @array)
    select @array_value = left(@array, @separator_position - 1)

    -- This is where you process the values passed.
    -- Replace this select statement with your processing
    -- @array_value holds the value of this element of the array
    select Array_Value = @array_value

    -- This replaces what we just processed with and empty string
    select @array = stuff(@array, 1, @separator_position, '')

    set nocount off

    Above it states replace the Select statment with your processing. Are they talking about an insert for example?

    Lets asume the following command. I have an SP

    CREATE PROCEDURE usp_insert

    @Ps_id varchar(5),
    @Ps_Cod varchar(50),
    @Ps_Name bit,



    VALUES (
    @Ps_id ,
    @Ps_Cod ,

    if the user selects multiple products in an ASP form how do I modify this to do that?
  6. Negative New Member

    The above example wasn't actually the one I was thinking of, but it will work. You will need to do some additional work in order to pass in 3 values, which will make this a little more complicated, although it can be done.

    However, in my opinion, XML is the best way to do this if you want to keep the entire order in one transaction. The comma seperated method gets more and more complicated as you pass more data into it. Changes are more difficult, and it is in general a debugging nightmare. I am curious about your statement "We dont use XML here." Is there a reason, or is it just something you haven't worked with in the past? If the code is going to go to the trouble to create a delimited list of values, it should be a simple task to change it to create an XML string instead, and the XML to pass to stored procedures is really easy to create and read.
  7. Twan New Member

    You can also just build up a sql batch and then submit the whole lot at ones


    ssql = 'exec sp1... ;'
    ssql = ssql + 'exec sp2...;'

    then submit the whole thing to SQL

    you can put other things in there too like begin trans, commit, etc.

    downside: you would have to concatenate in the parameter values, which can lead to SQL Injection... XML is the cleaner solution

  8. Raulie New Member

    Thanks, I want to stay away from SQL injections. As for as XML is conserned I have basic knowledge of it. I once read Learning XML (orielly) and took a 3 day course about a year and a half ago, never worked with it. I know a lot has changed since then. The company I work for uses ASP no XML support. How difficult do you think it would be to incorporate XML into our apps? Can I just build my projects to use xml or do I have to rearange all the apps? By the way can you give me an example on the XML part to do this? which setting do I have to configure to work with XML? We are using SQL Server 2000 with w2k.
  9. Negative New Member

    The XML for this is very simple. The stored procedure just accepts a varchar value with the XML string in it. Here is a simple example.<br /><br />CREATE PROCEDURE dbo.usp_InsertFromXml<br />(<br />@doc VARCHAR(1000)<br />)<br /><br />AS<br /><br />SET NOCOUNT ON<br /><br />DECLARE @idoc INT<br />EXEC sp_xml_preparedocument @idoc OUTPUT, @doc<br /><br />INSERT INTO MyPsTable (ps_id, ps_cod, ps_name)<br />SELECT ps_id, ps_cod, ps_name<br /> FROM OPENXML (@idoc, '/PS/OrderDetail', 2)<br /> WITH (ps_id VARCHAR(5) '@ps_id',<br /> ps_cod VARCHAR(50) '@ps_cod',<br /> ps_name BIT '@ps_name')<br /><br />GO<br /><br />Then you can call the stored procedure with the following:<br /><br />EXEC usp_InsertFromXml @idoc = '&lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />S&gt;&lt;OrderDetail ps_id="test" ps_code="testpscode" ps_name="0"/&gt;&lt;/PS&gt;'<br /><br />You can then include multiple OrderDetail elements in the document to insert multiple orders, for instance:<br /><br />EXEC usp_InsertFromXml @idoc = '&lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />S&gt;&lt;OrderDetail ps_id="test" ps_code="testpscode" ps_name="0"/&gt;&lt;OrderDetail ps_id="test2" ps_code="testpscode2" ps_name="0"/&gt;&lt;/PS&gt;'<br /><br />The XML string can be build in the asp application, and passed directly to your application.<br /><br />For more information on the details of OPENXML, I would recommend checking out BOL.
  10. Negative New Member

    And I forgot to mention, you shouldn't have to make any changes to your applications or SQL Servers to support this change, aside from changing the call to the stored procedure as shown above.
  11. Raulie New Member

    Very Cool thanks man. Cool, I played around with it for a while and I found it a lot cleaner than the other solutions.
    Hey can you explain this part... FROM OPENXML (@idoc, '/PS/OrderDetail', 2) what is the '/PS/OrderDetail' used for?

    Do you know of any good references where I can look to see how in ASP iam going to create this XML stream.

  12. Twan New Member

    Hi ya,

    /PS/OrderDetail says that it should look for the OrderDetail element within the PS element.

    NOTE that this is case-sensitive, regardless of how SQLServer is set up

    Are you using ASP or ASP.NET?

  13. Twan New Member

    Here is an excerpt from procs that we have in production.

    This is a calling proc which loads up the xml, checks what type of order we're dealing with and passes it a lower level proc to validate/process. In terms of passing it to a proc, you're better off using NTEXT rather than varchar/nvarchar, since you don't know beforehand how long the xml stream is going to be

    CREATE PROC dbo.proc_process_xml_order
    , @o_order_idintOUTPUT
    , @o_session_id_textvarchar(20)OUTPUT
    , @o_total_amountmoneyOUTPUT
    , @o_order_object_idintOUTPUT
    , @p_staff_IDint= NULL
    [email protected]

    EXEC sp_xml_preparedocument @idoc OUTPUT, @p_xml_dom


    IF (@type = 'estimate') BEGIN
    IF (@o_order_id IS NULL)
    -- add details of new order
    EXEC @return = dbo.proc_process_xml_order_estimate
    @p_idoc= @idoc
    , @o_order_id= @o_order_idOUTPUT
    , @o_session_id_text= @o_session_id_textOUTPUT
    , @o_estimate_ID= @o_order_object_idOUTPUT
    , @p_payment_method_text= @payment_method_text
    , @p_response_code_ID= @response_code_ID
    , @p_prepaid_account_ID= @prepaid_account_ID
    , @p_staff_ID= @p_staff_ID


    In the lower level proc, we load the results into variables so that we can validate them before actually insert them into the database. Reading XML-DOMs is better done as few times as possible for performance reasons

    DECLARE @estimate_item table (
    starting_referenceint NOT NULL
    , ending_referenceint NULL
    , is_completebit NOT NULL
    , copying_instructions_textvarchar(512) NULL

    -- fill estimate item table from XML
    INSERT INTO @estimate_item
    , ending_reference
    , is_complete
    , copying_instructions_text
    FROM OPENXML ( @p_idoc, '/order/estimate_item' )
    , ending_referenceint'end_document_reference/@ildb_ID'
    , is_completebit'is_complete'
    , copying_instructions_textvarchar(512)'copying_instructions_text'

    -- set estimate variables from XML
    [email protected]_text= medium_text
    , @colour_text= colour_text
    , @estimate_delivery_method_text= estimate_delivery_method_text
    , @order_delivery_method_text= order_delivery_method_text
    , @customer_ID= customer_ID
    , @customer_address_ID= customer_address_ID
    , @total_amount= total_amount
    , @source_text= source_text
    FROM OPENXML ( @p_idoc, '/order' )
    WITH (
    , colour_textchar(1)'colour_text'
    , estimate_delivery_method_textchar(1)'estimate_delivery_method_text'
    , order_delivery_method_textchar(1)'order_delivery_method_text'
    , customer_IDint'customer_ID'
    , customer_address_IDint'customer_address_ID'
    , total_amountmoney'total_amount'
    , source_textchar(1)'source_text'

    This is very cool stuff!!! It is incredible what you can do with this, and how efficient it actually is

  14. FrankKalis Moderator

  15. Raulie New Member

    Thanks fellow SQL studs. I spent the major part of my weekend reading up on XML support for SQL.

    By the way Twan we're using ASP not .Net

Share This Page