Post reply

Create a loop to pass a value from a table

  • November 4, 2016 at 12:49 pm

    #331770

    I need code to pass a value from a table to the store procedure.

    For example,

    Store procedure name: sp_order_historay (parameter: orderID)

    Table name: AllOrder

    How to create a loop

    1)select orderID from AllOrder and pass to sp_order_historay

    2)sp_order_historay will insert detail to another table when exec sp_order_historay

    (I completed sp_order_historay to receive parameter, processing data and then insert into new table)

  • November 4, 2016 at 1:31 pm

    #1910723

    Maybe something like this could help:

    DECLARE @sql nvarchar(MAX)

    SET @sql = ( SELECT N'EXECUTE sp_order_historay ' + CAST( orderID AS nvarchar(10)) + N';' + NCHAR(10)

    FROM AllOrder

    FOR XML PATH(''), TYPE).value('./text()[1]', 'nvarchar(max)');

    PRINT @sql;

    EXEC( @sql);

    More information on the concatenation method in here: http://www.sqlservercentral.com/articles/comma+separated+list/71700/

    I'm assuming that orderID is an integer column. If it's a string column, there's a small chance of being affected by SQL Injection.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • November 5, 2016 at 11:42 am

    #1910767

    If (and this is an assumption on my part) you plan on running your history procedure for every row in AllOrders, it's likely to perform poorly as the number of orders goes up. You may be better off taking a set-based approach and processing all the orders at once.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

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

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