The first thing I went about doing was creating the stored procedure. SSMS (SQL Server Management Studio) is a fairly easy tool to figure out, but in case you aren't familiar with it, here is the steps to create the stored procedure:
Step 1. Open Up the Query Window with the stored procedure standard code already generated
Step 2. Alter the template query so the stored procedure does what you want it to do. I have created a sample table (see design below) and a stored procedure which takes a customer number and returns the full name of the customer assigned to that number.
Step 3. Execute the code and verify that it is now in the list of stored procedures.
Now that the stored procedure exists, we can start to use it in our programs. In the sample program I created to demonstrate this, i have a label to display the full name and a button which will go through each of the customer numbers when clicked. Obviously, we can get much more abstracted than this, but it will do for demonstrative purposes.
Under the click event of the button, we have the code which will loop through the predetermined customer numbers and return to 0 once the end is reached. The GetCustomerName method is where we will call our stored procedure.
Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click If i < 9 Then i += 1 Else i = 0 End If Label1.Text = GetCustomerName(i) End Sub Private Function GetCustomerName(ByVal custNum As Integer) As String Try Dim ret As String = String.Empty Dim conn As SqlClient.SqlConnection = New SqlClient.SqlConnection("User ID = myUserName" & "; Password = myPassword" & "; Data Source = myServer" & "; Initial Catalog = myDatabase") Using cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand("TutorialSP", conn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@customerNumber", SqlDbType.Int).Value = custNum cmd.Connection.Open() ret = cmd.ExecuteScalar() cmd.Connection.Close() End Using GetCustomerName = ret Catch ex As SqlClient.SqlException GetCustomerName = "<SQL Exception>" End Try End Function
This small amount of code will result in the following simple app
The great thing about these calls to stored procedures is that you can do very complex calculations or table creations on the server side, then just pull the results back. You can return the results of a scalar query, a non query (insert/update), or a table and have as many parameters as you want. You have to be mindful of how long the query can possibly take to execute, however, so I would suggest that if you are going to do something that is going to take a while (or could) then execute it on a separate thread.
Another benefit of using stored procedures is that you can change the definition of certain properties without having to recompile and release a new version of your application. For instance, you may have a stored procedure which returns some calculation. You later learn that there is a much simpler way to calculate that same thing. Now all you have to do is go in and change the stored procedure and it filters down to each application which uses that calculation.
There are, of course, drawbacks to using stored procedures, but those are outside the scope of this tutorial/blog.
As always, i hope some found this post helpful. If anything is incorrect, you would like to know something more, or you found it helpful, please leave a comment. Cheers!