hi all,

im making reporting website using sqlserver2000 and c# asp.net. I have a big problem to make array(i dont no what is the equal to array in sqlserver) in sqlserver. Normaly i made array list in codebehind and pass it to my storedprocedure one by one. But it wasting time to generate my report.

I tried to use CURSOR inside the stored procedure to do samething inside the sp. But it wasnt work well.

Anybody know to do this inside the SP.Please explain to me how i can do it and give me a related links to get more idea.

thanks

__________________
MSB

SQL Server doesn't have a native type for arrays. As far as I know you have three options:

• Use a procedure with a lot of parameters, Microsoft use this technique but it looks messy and you need to be sure that there will always be enough parameters for each member of the array
• Pass the data as an XML type (or string representation in 2000)
  Code:

  <array>
    <item>item value here</item>
    <item>item value here</item>
  </array>

  I've used this and it's not too difficult in SQL Server 2005 but can be messy in 2000
• Use a CLR custom type, only works for 2005; never tried this myself but should be possible

--

Joe (Microsoft MVP - XML)

What on Earth are you doing in a GUI that would need to pass an array of information to a database? Please tell me you're not trying to parse a text file or some such...

--Jeff Moden

HELLO JEFF......

THIS IS MY PROBLEM. I HAVE BRANCH CODE IN MY DROPDOWN LIST. I WANT TO FIND SELECTED BRANCHCODE UNDER CODES INSIDE THE SP. AND HAVE TO GET THERE INFORMATIONS(SUBBRANCH).
NOW IM DOING IT IN MY C# ARRAY. AND ARRAY HAS MY SUBBRANCH(WHOS UNDER SELECTED BRANCH).

EX-: SELECTED BRANCH = "A"
      I WANT TO FIND "A.1,A.1.1,......A.2,....A.3,...
ITS LIKE TREE.

That explains what you're doing in the GUI... but I still don't understand what your trying to pass to the DB as an array... are you saying that you're trying to pass the information in the SubBranch? If so, you may have this whole thing backwards... shouldn't everything in your "tree" already be located in the database? Shouldn't you just be passing a single parameter to find the info in the database?

--Jeff Moden

We pass arrays quite often. The common case is when the customer is shopping and we save details of a basket, it seems better to send an XML representation of the basket and just use one stored procedure than to send each item individually. The second case is addresses. Our customers can register multiple addresses at once and we send them to a single procedure for processing. Surely this is more efficient and more atomic than one update/insert per address?

--

Joe (Microsoft MVP - XML)

That's cool, Joe... and I understand that need. But, I don't understand what msbsam is doing or why. I'd like him/her to tell us what he/she is trying to do so we can figure out what the best solution is...

How 'bout it msbsam? Can you be a bit more descriptive of the array you're trying to pass and why you need to pass it?

--Jeff Moden

I forgot another option, passing something like a comma separated string and parsing it. Similar to the XML method, more advice here http://www.sommarskog.se/arrays-in-sql.html.

--

Joe (Microsoft MVP - XML)

First i want to tanks both of you.
 This is my problem.
I DONT WANT TO MAKE ARRAY INSIDE MY C#(This is my major problem.If i make array in c#, reports are very slow).

I have 6 dropdownlist in my GUI(I didnt tell this b4:(). That all are loading my page load event. dont think about it.
if i click my first dropdown list item , then my next all dropdown list want to fill subcodes of that selected item. It mean my selected dropdown list item is now supper and otherdrop down list items are sub branch.
this is a main structure of my GUI.

This is my problem. If I pass selected branch (1st dropdown list selected item) to stored procedure it only finding that dropdown subitems. next I wnt to pass this subitem(next time I want to this sub item like supper) to find there sub item.

In c# i can do it using Array list.

I think you have knowledge what is happening arrays in programming language. I want to do same operations inside the sp or any other function.

Please if you still dont clear this ask me.
I need yours help.
thanks both of you.

I'm trying to understand this myself. Even if you had 10 drop-down lists with selected items it seems that you could pass the values without an array. I still don't get the array thing. I think you're going to have to post some of the code so we can know what you're talking about.

Thanks,
Richard