Run SQL Stored Procedure

[IndigoMontoya]

My SQL stored procedure, runs 4 select statements. When I run the stored procedure in SQL it returns results of all the stored procedures. When I try to return the data to the datagridview in Visual Studio, it only runs the 1st select statement. What do I need to modify in order for it to return all of the stored procedure?

Thank you to all who view and reply.

 

[Lotok]

Your dataset will contain 4 tables, each a result from a select statement. If you want ALL results as 1 table and to show on to 1 grid, you could declare a table variable in your stored procedure, and have the 4 statements select into that table. Then select all from the table variable so you get 1 table of results.

 

[IndigoMontoya]

Your dataset will contain 4 tables, each a result from a select statement. If you want ALL results as 1 table and to show on to 1 grid, you could declare a table variable in your stored procedure, and have the 4 statements select into that table. Then select all from the table variable so you get 1 table of results.

Okay here is my code:

Dim connetionString As String

        Dim connection As SqlConnection

        Dim adapter As SqlDataAdapter

        Dim command As New SqlCommand

        Dim ds As New DataSet

 

        connetionString = "Server=Titanium;Database=TestDB001;User ID=sa;Trusted_Connection=Yes;"

        connection = New SqlConnection(connetionString)

 

        connection.Open()

        command.Connection = connection

        command.CommandType = CommandType.StoredProcedure

        command.CommandText = "dbo_Test_USP"

        adapter = New SqlDataAdapter(command)

        adapter.Fill(ds, "connection")

        Form2.DataGridView2.DataSource = ds

        Form2.DataGridView2.DataMember = "connection"

What would I need to change in order for it to display as you stated?

 

[Lotok]

The change would be the T-SQL in your stored proc.
Post your procedure and I will show you

 

[IndigoMontoya]

Here are two of the select statements...I can post thte other two if needed.

USE [Testdb]
GO
ALTER PROCEDURE dbo_Test_USP
AS
BEGIN
--Select Statement1
SELECT CONVERT(VARCHAR(20), OrderDate, 101) AS [Part Ordered Date], Customer, CustomerAddress, CustomerPhone, CustomerZip, CustomerCity, CustomerState, CustomerCountry, CustomerEmail, CustomerOrderCount, ID, PartID FROM tblOrderHistory WHERE OrderDate IS NOT NULL

--Select Statement2
SELECT Customer, CustomerAddress, CustomerPhone, CustomerZip, CustomerCity,  CustomerState, CustomerCountry, CustomerEmail, CustomerOrderCount, ID FROM tblPreviousOrders, tblOrderHistory INNER JOIN tblPreviousOrders.Customer = tblOrderHistory.Customer

 

[Lotok]

An example of what I mean...

    GO
    ALTER PROCEDURE dbo_Test_USP
    AS
    BEGIN
    
    DECLARE @TMP TABLE(Customer varchar(128), Address varchar(128), ID int, Zip varchar(10))
    
    INSERT INTO @TMP (Customer, Address, ID, Zip)
    SELECT Customer, [Address], ID, Zip FROM Test1
    
    INSERT INTO @TMP (Customer, Address, ID, Zip)
    SELECT Customer, [Address], ID, Zip FROM Test2
    
    SELECT * FROM @TMP

 

[IndigoMontoya]

So create a temp table that houses all the data, then export via .Net from the table? Is that the best way to go about it?

 

[Lotok]

Re: Run SQL Stored Procedure with VB.Net

table variable is better than a temp table. SQL well store the execution plan making it faster than adhoc queries. It also means you get it all on one table in the dataset.


Sent using XT910 with Android 4.0.4 and MIUI 2.8.10

 

[IndigoMontoya]

Works perfectly, thank you for your time and words of wisdom.

 

[Lotok]

No Problem, you killed my father, prepare to die!