How to write Stored Procedure that gives multiple rows?

[sdamle]

I want to write a Stored Procedure that gives me multiple rows of data and execute it in VB.

Can you tell me how to do that?

Your help is greatly appreciated.

 

[tlbroadbent]

What do you need to know - how to write the stored procedure to return rows OR how to execute it from VB OR all of the preceding? Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[sdamle]

Actually I want to know both. Writing a stored procedure that gives multiple rows and then execute it through VB and display the rows.

 

[tlbroadbent]

If all you need to do is return rows from a table or tables, you don't need to execute a stored procedure. Just query the table or create a view and query the view from VB.

Read SQL Books Online for details about creating stored procedures.

http://msdn.microsoft.com/library/en-us/tsqlref/ts_create_4hk5.asp

The following is a SIMPLE example That returns all rows from a table.

Create Procedure MyProc1 As

Select * From MyTable
Go

Here is an example that accepts a parameter and uses the parameter as criteria for selecting rows.

Create Procedure MyProc2 @CompanyID Int As

Select * From MyTable
Where CompanyID=@CompanyID
Go

The article at the following link gives an excellent introduction to ADO and SQL Server. You can even download a sample.

http://msdn.microsoft.com/library/en-us/dnsqlsg/html/msdn_adosql.asp

Hope this info helps. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[cambam]

This is actually rather simple. When a stored procedure that is written as a select statement is returned from VB, it is returned as a recordset.

First create your stored proc and make sure it's installled on SQL Server See the example in this thread. If it's a simple stored proc without parameters, just call it how you would call any recordset: rs.open "proc_name_here", conn

If it's a proc with input parameters, you need to create a command object first. Then do as follows:

With cmdProc
.CommandText = "proc_name_here"
.CommandType = adCmdStoredProc
.ActiveConnection = adoConn
End With


Set rsData = cmdProc.Execute(, Array("parameter_here", "parameter here&quot)

Then treat the RS as you would any other.

 

[sdamle]

Thank you guys very much. I will give it a shot and let you know how it goes. Appreciate all your help.