Execution Process of Stored Procedure

Stored Procedure

So friends I am currently working on creating procedures so I did some research research of articles and ended with this. So I want to share the knowledge I obtained with you. I will be happy if you can use this information, it will be useful for me and I will appreciate your feedback.

• What a Stored Procedure is
  
  
• A Stored Procedure is a set of precompiled SQL statements saved in the database server (SQL Server, Oracle, DB2 and so on).

Before going further let's explain how the query actually flows through the database and how our Query output is generated in SQL Server.

How the query is Executed

                                             Figure 1.0 Execution of Query

From the preceding diagram we can predict that the step-by-step execution process is shown above, When a command is executed first its syntax is checked, whether it's syntactically correct. If it is then a plan is selected based upon the indexes and then finally the command is executed. It seems like a lengthy process if we continue to execute the same command many times. What if the two steps (Syntax checking and Plan selection) are omitted and the plan is directly replaced by a cache where it will be inserted and the query will again execute with the proper plan from the Cache rather than executing the entire 3-step process? We will figure this out in the following diagram:


Figure 2.0 What if, when the user sends the second request, we skip the two steps and use the Cached Plan again and again then that is where Stored Procedure are useful.

The need for Stored Procedures

1. First of all when we want to implement abstraction of database information from the developer (adding a layer of security that can be placed in the database from the calling application).
   
   
2. To avoid SQL injection we use a Stored Procedure (to help prevent hacking directly into the database tables).
   
   
3. When you execute the same set of queries on a daily basis, in other words these queries may be a simple select command, insert command and so on. Intead each time you execute your command it's better to insert the best plan into the cache and then use the plan from the cache many times.

Let's figured it out Stored Procedure actually works

SQL Profiler is a tool that tells us what kind of SQL is running in SQL Server and also helps us to track what is related to SQL Server, like SQL Started, SQL Ended (Batch Starting/Ending).

Step 1



To check whether after creating the Procedure the SQL Server automatically does use the cache process as explained above.

We will uncheck all the already checked Events and add an event for SP:CacheInsert (inserted plan into the Cache), SP:CacheHit (has the cache been used or not?).

Step 2



Click in Show all events.

Step 3



Step 4

And to add on the description we will add one more feature to go in depth for the procedure that the application fired these events and that SQL was executed with a plan.

Click on Show all Columns:







And since SQL Server is automatically performing its background process, Filters limit the events collected in a trace so as to collect the required tracing events for the cache and the plan we will apply a filter as shown below:



And also the database is AdventureWorks2008R2 that I am using for a specific database table you are creating a procedure.



Press OK and then click Run

The SQL Profiler runs and waits for the query to be executed, once the user runs the query we will find how the SQL Server profiler caches the query as shown in the following figure.



We can clearly see that we have already selected SP:CacheInsert so here we can clearly see how SQL Server caches the query.

Now I execute the query again and we will find that this time a SP:CacheHit event has occurred (found the plan for query execution).



Now the questions are, when we are aware of the plan creation and the query has been cached then why create a procedure? Let me answer that.

Let's make a slight change in the query and add one more field for more bifurcation.

I added one more predicate or input value for more bifurcation:

select * from Person.Person where FirstName='Ken' and BusinessEntityID='2300' and PersonType='GC';

We found that the SQL profiler creates a new SP:CacheInsert event for the query hence we found that the SQL Server creates a Plan + Cache for the plan but once we make a minor change to our query the SQL Server creates a new CacheInsert event for it. We can also say that once we change our query and add or decrease the Input value the SQL Server will not use an initial plan but it will create a new plan by itself, in other words it is not using a SQL plan from the cache.



Now we will create a new Stored Procedure for the same query.

Go to the Stored Procedures folder.



Right-click on the Stored Procedure.



We will have the following window.



The following is the default example of the Stored Procedure.

Now let's make some changes in this and make our first Stored Procedure.



Here I have created a procedure for retrieving information from the table based on its FirstName and BussinessEntityId. 

• Syntax Create Procedure <Procedure Name>.
  
  
• Declare the variables or input parameters that will hold the values input by the user, for example @FirstName declared as Varchar(50) and @BusinessEntityId as int. (Note: You can define the variable depending on your declaration or understanding.)
  
  
• Select command following the input name along with their respective variable name.

Once it is done we execute and the commands are successfully completed or executed. Congrats, our first procedure was successfully created.



In the Stored Procedure section we will find that one Stored Procedure has been created as shown below in the figure.



Now we will execute the Stored Procedure along with an input parameter value.



Just make the SQL profiler clear. I am clearing the old tracing information by clicking on Edit-> Clear Trace Window.

So now since we executed our procedure, SP:CacheInsert has happened, in other words the plan has been created and inserted into the cache and the Stored Procedure was executed with the results.



Now we will change the value of the input parameter and check whether the inserted plan is used or not. In other words whether the plan that has been inserted into the cache is being used or not.

Now when I change my parameter value it should actually go and hit the SP:CacheHit the plan that has been stored in the Cache.



We found that it has taken plan from the cache. The Stored Procedure is using the plan from the cache. It's not recreating the plan again and again as we found in normal queries.

Advantages

• Performance as it plan is taken from the cache.
  
  
• Centralized maintainsed or code (changed in one place and automatically it will change everywhere).

Parameters

There are 3 types of parameters. Since we have already declared input parameters to receive the value from the users, there other types of parameter in a Stored Procedure that are called Output Parameters.

An Output Parameter helps you to retrieve data back to the user who is calling the Stored Procedure.

For example we want the current date and time when the Stored Procedure was executed.

So will declare an output parameter along with input parameter as shown below:



Now we have declared a new output parameter called @DateTime that will collect the DateTime and will be returned as an output parameter to the user and displayed in the messages section. To change the Stored Procedure we use an Alter Command and execute it.



Now here we found that the output parameter @DateTime is returning a value that has been collected via using @DateCollector as Datetime and printed the value of it. Now there is another third type of parameter that is called Return parameter.

We can have multiple input parameters as shown in the preceding and we can also have multiple output parameter but in the case of a return parameter we can only have one parameter.

Now here we are using a return @@RowCount. The number of rows affected by the last command. @@Rowcount is set to 0 by any command that does not return rows, such as an if statement. With cursors, @@Rowcount represents the cumulative number of rows returned from the cursor result set to the client, up to the last fetch request. Return type: int.



Return parameter is written inside the Begin block.

We declare a parameter to collect value of RowCount in parameter @Rowcount and print it.



It has been a lengthy article. I thank you for your patience for reading, but I hope this will help you to have in-depth knowledge of Stored Procedures.