Looping through parameters 1

[jimmythegeek]

Here is what I need to do. First I need a developer to pass me about 30 ID/Value pairs on which I need to loop through and add a new row (insert statement) for each pair. What is the most efficient way to do this? I do not know how to loop through input parameters, or if it is possible, and I also need the parameters to have 2 parts. I hope this makes sense. Thanks for any insight.

Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[SQLSister]

Passing a list of values to a Stored Procedure faq183-3979
Passing a list of values to a Stored Procedure (Part II) faq183-5207


Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[donutman]

Create a stored procedure that inserts one pair at a time. Have the developer call it 30 times. Building a string and parsing it with the above function, IMHO, is not a good programming practice. Also, that procedure will crash if one of the values has an embedded delimiter. Besides it's less efficient.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[jimmythegeek]

Donutman,

I had thought about doing it that way, but is calling the procedure 25 or 30 times the most efficient way to do it? I am not a fan of the parsing method, it would be nice if you could just loop through the parameters in the stored procedure doing an insert for each one.

Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[donutman]

If this is a mission critical 24/7 application with a high volume of inserts, then you might need to study the efficiency issue closely; is network traffic a bottleneck or the SQL Server? You can reduce network traffic by combining the parameters, but you then lose a bit of efficiency with the T-SQL.
If there is not any special considerations, I would opt for the Best Practices in programming. Again, IMHO, it is best to do single updates in a SP. The success of each update can be evaluated with each iteration. If the block of inserts are a single transaction, then that alters the equation slightly...let us know on that.
SQLSister, what's your opinion on the issue?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[jimmythegeek]

I understand what you are saying. This is definitely NOT a 24/7 critical app, and I believe I will go with your multiple calls method. It is a very straight-forward SP with a single insert, and should not cause performance issues. Thanks for your input.

Jim Lunde
We all agree your theory is crazy, but is it crazy enough?