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...
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.
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.
Thanks & Regards,
SP
Please do "Mark As Answer" if this helps you.
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 :)
Marked as answer by Ming Xu - MSFT on May 07, 2012 10:11 AM
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()
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.
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 :)
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
Anand Reddy
Member
8 Points
67 Posts
What is the better way to call multiple Stored Procedures
Apr 30, 2012 12:02 PM|LINK
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...
praseTech
Member
198 Points
51 Posts
Re: What is the better way to call multiple Stored Procedures
Apr 30, 2012 02:02 PM|LINK
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.
2177
Member
375 Points
325 Posts
Re: What is the better way to call multiple Stored Procedures
Apr 30, 2012 02:12 PM|LINK
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.
SP
Please do "Mark As Answer" if this helps you.
Loganix77
Participant
1351 Points
412 Posts
Re: What is the better way to call multiple Stored Procedures
Apr 30, 2012 02:13 PM|LINK
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 :)
Anand Reddy
Member
8 Points
67 Posts
Re: What is the better way to call multiple Stored Procedures
Apr 30, 2012 03:28 PM|LINK
Can you send me sample code. So that I can call multiple sp as transaction..
Loganix77
Participant
1351 Points
412 Posts
Re: What is the better way to call multiple Stored Procedures
Apr 30, 2012 07:22 PM|LINK
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:
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()Loganix77
Participant
1351 Points
412 Posts
Re: What is the better way to call multiple Stored Procedures
Apr 30, 2012 07:29 PM|LINK
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.
Loganix77
Participant
1351 Points
412 Posts
Re: What is the better way to call multiple Stored Procedures
Apr 30, 2012 07:37 PM|LINK
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 :)
Loganix77
Participant
1351 Points
412 Posts
Re: What is the better way to call multiple Stored Procedures
Apr 30, 2012 07:44 PM|LINK
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: