How to Pass an array from the front end (say .NET) into a Stored Procedure?

Question

Post reply

How to Pass an array from the front end (say .NET) into a Stored Procedure?

pramod_yg

Ten Centuries

Points: 1005
More actions
April 13, 2010 at 7:33 am

#138475

How is it possible? is there any way that we can make this happen?
Pramod

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

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Need a little more information in order to even start assisting you.

Is it a single dimension array or a multiple dimension array?

Also .. some sample data (Items) the array will contain would be helpful.

With that information the experts here can begin to answer your question in a meaningful manner.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

pramod_yg Ten Centuries Points: 1005 More actions · Answer 2

See, i have a table which has

employeenumber, day1effort, day2effort, day3effort, day4effort, day5effort, day6effort, day7effort

This table has some 100 rows.

Now, we are calling the SP for each row and the data is being passed as parameters to the SP.

since, it has 100 rows, the sp gets called 100 times. I dont want to do that. I want to send the complete table array to the SP so that the SP is called only once.

Please let me know if anything else is required.

Pramod

Ronald H SSCarpal Tunnel Points: 4053 More actions · Answer 3

I know you posted in the 2005 group, but 2008 has probably the ideal solution for your problem: Table Valued Parameters. Or else I would make a second procedure that executes in bulk.

Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2

Ron McCullough SSC Guru Points: 63877 More actions · Answer 4

I would strongly suggest you read this article by Jeff Moden and see if it is applicable to your situation

http://www.sqlservercentral.com/articles/T-SQL/63003/

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

WayneS SSC Guru Points: 95460 More actions · Answer 5

There are a few methods, but none of them are pretty. Note that in SQL 2008, you can pass a table variable to a procedure, but since this is a 2005 forum:

Note that these are listed in the order that I'm thinking of them.

1. Create and populate a table. Procedure references this table (but you still have to populate it row-by-row).

2. Convert the data into XML. Pass the XML to the procedure.

3. Convert the data into a delimited string, and pass it to the procedure.

This articles might be of interest to you:

Passing Parameters as (almost) 1, 2, and 3 Dimensional Arrays[/url]

Using XML to pass Multi-Select parameters from SSRS to SQL Server [/url]

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2