Solved

array output from stored proc?

Posted on 2003-11-10
14
882 Views
Last Modified: 2008-01-16
How do I return an array from a stored procedure?
A short example?

Thanks.
0
Comment
Question by:samliam
14 Comments
 
LVL 22

Expert Comment

by:CJ_S
ID: 9720681
There are no arrays in stored procedures. You do have recordsets.

What kind of information do you need to return?
0
 

Author Comment

by:samliam
ID: 9720819
I want to return one column of a table.
0
 
LVL 6

Expert Comment

by:lausz
ID: 9721844
From this link :

http://www.microsoft.com/technet/treeview/default.asp?url=/technet/itcommunity/chats/trans/sql/sql0724.asp

Host Guest_ken_ms
Q: Pancha - How can I use arrays as Input parameter in a Store procedure?

A: You can't - there are no array types in T-SQL. You can simulate arrays using delimited strings or XML strings (or the UDFs/xprocs in my last book ;-) but there's no built-in way to do it.



Host Guest_ken_ms
Q: tc : in sql server 7.0, what is the best way to get around not having an array datatype, if I need to pass an array into a sproc

A: You can use a delimited string and shred the string into 'elements' or you can use the xprocs in my last book :-) The best strategy is probably not to do this unless there's no other way.

0
 
LVL 29

Expert Comment

by:leonstryker
ID: 9722066
Depending on what you plan to do with the data once you retrieved it, you may may keep it in a recordset, which is an array of fields and rows, in your code; or if you are still in SQL you may keep the results in a temp table for further manipulation.

What are you trying to accomplish with your code?

Leon
0
 

Author Comment

by:samliam
ID: 9723624
I want to put the column in a dropdown list.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9723638
Please maintian your many old open questions:

1 04/04/2001 50 monitoring system resources  Open Windows Programming
2 02/03/2002 100 read pdf from IE6  Open Windows NT
3 10/13/2003 50 automatically generated statement  Open C#
4 10/13/2003 50 the Duwamish online project  Open C#
5 10/20/2003 100 webform creation at runtime?  Open C#
6 09/25/2001 50 network cards  Open Windows NT
7 10/07/2003 125 IE6 runtime error  Open JavaScript
8 10/07/2003 20 IE6 debug?  Open Browsers Issues

Thanks,
Anthony
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 9724182
Assuming you are using VB, you would need to loop through the recordset while adding the items, something like this:

rsData.MoveFirst
While Not rsData.EOF
    lstBox.AddItem rsData(0)
    rsData.MoveNext
Loop
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:samliam
ID: 9724673
I use SqlCommand to execute a stored proc.
I use a SqlDataAdapter to 'Fill' a dataset. SqlDataAdapter contains a selectCommand. What should the selectCommand be, now that I am calling a stored proc?

If I can get the column from the stored proc into a dataset, how do I read the dataset? what is the table name? (e.g., ds.Tables["?
"])

SqlCommand takes a connection. SqlDataAdapter also takes a connection. What happens with 2 connections?
0
 
LVL 29

Accepted Solution

by:
leonstryker earned 50 total points
ID: 9724833
samliam

You are using VB.NET and ADO.NET. Since you are only trying to populate a list box your best bet is DataReader object.  Look at the following links on how to do that.  

ADO.NET tutorial --> http://samples.gotdotnet.com/quickstart/howto/doc/adoplus/adoplusoverview.aspx
VB.NET sample ----> http://samples.gotdotnet.com/quickstart/util/srcview.aspx?path=/quickstart/howto/samples/adoplus/adooverview3/adooverview3.src

Instead of:

      do while (myReader.Read())
        Console.Write(myReader("CustomerID").ToString() + "    ")
        Console.WriteLine(myReader("CompanyName").ToString())
      loop

in the sample code you will need to do something like this:

      do while (myReader.Read())
         lstBox.AddItem(myReader(0)ToString() + "")
      loop

Leon



0
 

Author Comment

by:samliam
ID: 9724837
Reason I use SqlCommand to call the proc is that I need to pass in a paramete.
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 9724870
Look at the VB.NET sample link above.
0
 

Author Comment

by:samliam
ID: 9725038
That works! Thanks.
What if I want to use SqlDataAdapter?
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 9725198
0
 

Author Comment

by:samliam
ID: 9725487
I figure that one out. I still feel that SqlDataAdapter is a strange class. I had to use Tables[0] and Rows[i][0] since I don't know what the table name should be..

Thanks for the help. I am crediting the points now.

Would you also be able to answer my other question regarding how to make the table name in a stored proc a parameter to be passed in? Here is the link to the question:

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20793984.html
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now