What is the better way to call multiple Stored Procedures

Hi,

I have a web page with some controls

1) text boxes

2) labels and dropdownlists

Totally I have 4 stored procedures (sp) to use.

a) First sp is used to fetch data to fill in textboxes ( this data come from properties after sp has executed in DAO layer)

b) Second sp is used to fill all the 20 dropdowns (returns DataTable using which all dropdowns are binded)

c) Third sp is used to display 20 labels text dynamically (each label is located in front of one dropdown) (returns DataTable using which we bind data to all labels)

d) Fourth sp is used to select the dropdown selected item

The scenario how I am calling all the stored procedures is..

In the page_Load -> I am calling First sp, if the datatable which it returns has count >0 i'm sending a boolean value true to code behind.

If the boolean value is true, I am calling second and third sp and filling dropdowns and labels text,

finally I am checking the condition which item should be selected in the drop down list..

Every time I am calling a new function to execute stored procedure.

Is this the correct scenario what iam doing??

If not then please tell me a better way to call...

Re: What is the better way to call multiple Stored Procedures

Are you planning to call multiple sp from one another? If no then use transaction scope at the code level to ensure that the code work in transaction. It should complete enitrely or nothing.

Re: What is the better way to call multiple Stored Procedures

Create a new wrapper proc. This proc can call all your 4 procs based on some conditions you may want to apply. So the proc will return DataSet and in code you can do whatever you want to with this DataSet.

This way all you need is just call the wrapper proc from the code.

Re: What is the better way to call multiple Stored Procedures

If your SP's very fast to populate you may want to consider only making one pass at the DB. You could add all your parameters at once if possible and load all of your result sets into a DataSet on your web server. You can then check your dataset results and execute your conditional codes based on the values in your datatables inside your dataset. Your dataset would then act like a temporary database housing the temporary tables you created with one pass on your DB.

I personally prefer to limit the number of times I need to hit the database on a page. By using a dataset with several datatables populated by your SP you would only have to hit the DB once to pull all your data.

So your SP would Fill your Dataset, in the order your result sets are created.

So Query1 in the SP would be DataSet.DataTable(0)

Query2 to would be DataSet.DataTable(1) and so on

There isn't really anything "wrong" with the way your doing it now, but by retreiving all of your records in one pass on the Database if possible, you limit the number of connections being made to the DB which is normally a good thing :)

Re: What is the better way to call multiple Stored Procedures

Can you send me sample code. So that I can call multiple sp as transaction..

Re: What is the better way to call multiple Stored Procedures

Ok for example.

Here I have an SP that pulls to recordsets back for our customer service advisors. We want to see detailed sales information and we want to see summary information for their budgets:

USE [MYDataBase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_CSA_Budget]
(
@ytdDate1 nvarchar(8),
@ytdDate2 nvarchar(8),
@mtdDate1 nvarchar(8),
@mtdDate2 nvarchar(8),
@CSA nvarchar(4),
@vMonth Integer
)

As

Declare @command as varchar(8000)

Declare @tblSales as Table (CSA nvarchar(10),Br nvarchar(5),CustNo nvarchar(50), CustName nvarchar(200),MTDParts Float, MTDService Float,
MTDTotal Float, YTDParts Float, YTDService Float, YTDTotal Float)

BEGIN
	
set @command = 'Select Statment Here'

Insert Into @tblSales
Exec(@command)

Select * From @tblSales

Select CSA, Sum(MTDTotal) as TotalMTD, (AnnualBudget/12) as MTDBudget, (Sum(MTDTotal) - (AnnualBudget/12)) as MTDVariance,
Sum(YTDTotal) as TotalYTD, ((AnnualBudget/12) * @vMonth) as YTDBudget, (Sum(YTDTotal) - ((AnnualBudget/12) * @vMonth)) as YTDVariance,AnnualBudget
From @tblSales as a Inner Join CSASalesmen on a.CSA = CSASalesmen.CSANo
Group By CSA, (AnnualBudget/12), ((AnnualBudget/12) * @vMonth), AnnualBudget

Return

END

Now when I call that SP from my code I can populate two gridviews, one with details and the other with summary. You can databind any to any valid control (DLs, GVs, etc.) for this example I bind to two different gridviews. But you could apply this same logic to your scenario.

Dim cnn As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("MyString").ConnectionString)
        cnn.Open()
        Dim sqlAdp As New Data.SqlClient.SqlDataAdapter
        Dim cmd As New Data.SqlClient.SqlCommand
        Dim ds As New Data.DataSet
        
        cmd.CommandTimeout = 600
        cmd.Connection = cnn
        cmd.CommandText = "usp_CSA_Budget"
        cmd.CommandType = Data.CommandType.StoredProcedure
        cmd.Parameters.Clear()
        cmd.Parameters.AddWithValue("mtdDate1", mDate1.ToString("yyyyMMdd"))
        cmd.Parameters.AddWithValue("mtdDate2", mDate2.ToString("yyyyMMdd"))
        cmd.Parameters.AddWithValue("ytdDate1", yDate1.ToString("yyyyMMdd"))
        cmd.Parameters.AddWithValue("ytdDate2", mDate2.ToString("yyyyMMdd"))
        cmd.Parameters.AddWithValue("CSA", selCSA)
        cmd.Parameters.AddWithValue("vMonth", DatePart(DateInterval.Month, mDate1))
        
        sqlAdp.SelectCommand = cmd
        sqlAdp.Fill(ds)
        
        cnn.Close()
        
        BudgetSummary.DataSource = ds.Tables(1)
        BudgetSummary.DataBind()
        
        BudgetDetails.DataSource = ds.Tables(0)
        BudgetDetails.DataBind()

Re: What is the better way to call multiple Stored Procedures

In my SQL example you'll see that I'm declaring my SQL statement as an nvarchar variable. My webserver communicates with our business system via a linked server connection. Because of this I have to use OpenQuery() to query our business system. SPs are not very friendly with the OpenQuery process but it will execute the command if it's stored as a variable and then issued the exec(command) statement.

Assuming you're not using linked servers you don't need to store the command in a string and execute it, you can just simply place your query in your SP. You'll see that all I'm doing is issuing two different select statements which returns two different recordsets. Then I fill a dataset with the SP and datatable(0) is the first record set returned and dataset(1) is the second returned and then you just databind. I've filled two gridviews, with two different select statements, but I've only called the connection to my DB once.

Re: What is the better way to call multiple Stored Procedures

On a side note, talking about only making one call to the database. You'll see in my SQL SP that I'm storing my intial query into a temporary table. What I'm doing here is telling my SP to collect all the relevant information I need in one query. I use that data to create a temporary table variable in my SP. Then I issue queries against that temporary Table to populate my recordsets. So my Page makes a single call to my database for the information it wants for the report, and my database makes only a single call to the remote database as well.

I just feel it's always best to keep the number of connections made to a minimum :)

Re: What is the better way to call multiple Stored Procedures

Here's a sample of using transact code to run multiple SPs. The SPs in this code are performing update commands on my database so I've got the transact code setup to rollback any transactions if a part of the process fails. It also catches the error and stores it so I can keep track of any failures. This is probably a little more than what you're needing for what you're trying to accomplish:

USE [MyDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Will B
-- Create date: 01/30/2009
-- Description: Transact Proc To Update Rental Profit Report
-- =============================================
ALTER PROCEDURE [dbo].[usp_RentalProfitUpdate]
(
@DateNum1 varchar(8),
@DateNum2 varchar(8),
@DateShort1 varchar(10),
@DateShort2 varchar(10),
@mrun varchar(2),
@yrun varchar(4),
@PeriodYear varchar(8)
)

As

Begin
	Begin Try
		Begin Tran
			Exec usp_RFProfitUpdate1 @DateNum1,@DateShort2,@mrun,@yrun
			Exec usp_RFProfitUPdate2 @DateNum1,@mrun,@yrun
			Exec usp_RFProfitUPdate7 @DateNum1,@mrun,@yrun
			Exec usp_RFProfitUpdate3 @DateShort1,@DateShort2,@mrun,@yrun
			Exec usp_RFProfitUpdate4 @mrun,@yrun
			Exec usp_RFProfitUpdate5 @PeriodYear,@mrun,@yrun
			Exec usp_RFProfitUpdate6 @mrun,@yrun
		Commit Tran
	End Try
	Begin Catch
		If @@Trancount > 0 Rollback
		Insert Into RentalProfitabilityTblErr(DateOfError,ErrorNum,ErrorProc)
		Values (GetDate(),Error_Number(),Error_Procedure())
	End Catch
End