Using stored procedure on processing huge query can make data processing faster compare to processing the data on the client side. A stored procedure is a precompiled executable object that contains one or more SQL statements. When calling stored procedure, you are issuing just one batch of commands and the database server executes all the commands and returns the result to the calling client application. This approach has a huge optimization in case the database server is accessed via a slow network.

Another advantage of using stored procedure over client queries is, in stored procedure you don't have to recompile and redeploy your application whenever there are changes on your queries. Instead you just edit the stored procedure directly on database server query editor and run it then it will automatically reflect to all application using that stored procedure.

To show you how to call Stored Procedure in VB.NET, here is a sample code that counts the recurring invoices that were already due.


The Stored Procedure to call.

ALTER PROCEDURE [dbo].[uspCountRecurInvoice] @RecurCount float OUTPUT

AS
SET NOCOUNT ON;

--monthly
SELECT @RecurCount=COUNT(ARHeaderID)
FROM dbo.tblARHeaderRecur
WHERE DateDiff(mm,StartDate,GetDate())>=1 and RecurringInterval='Monthly'

--weekly
SELECT @RecurCount=@RecurCount+COUNT(ARHeaderID)
FROM dbo.tblARHeaderRecur
WHERE DateDiff(wk,StartDate,GetDate())>=1 and RecurringInterval='Weekly'

RETURN @RecurCount

Private Function CountRecurringDue() As Boolean
Dim con As New SqlConnection
Dim cmd As New SqlCommand
Dim lreturn As Boolean = True
Try
con.ConnectionString = Settings.ConnectionString
con.Open()
cmd.Connection = con
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "uspCountRecurInvoice"
cmd.Parameters.Add("@RecurCount", SqlDbType.Int)
cmd.Parameters("@RecurCount").Direction = ParameterDirection.Output
cmd.ExecuteNonQuery()
Dim intCount As Integer = 0
Integer.TryParse(cmd.Parameters("@RecurCount").Value, intCount)
If intCount > 0 Then MessageBox.Show("There is " & intCount.ToString() & " recurring invoice to run", "Recurring Invoice")
Catch ex As Exception
lreturn = False
MessageBox.Show("Error while counting due recurring invoices." & ex.Message, "Recurring Due Counter")
Finally
con.Close()
End Try
Return lreturn
End Function

Labels: Coding Tips and Tricks, Microsoft SQL Server, SQL, VB .NET