TSQL Tutorial 17: Recompiling Stored Procedures, strength and weak points of stored procedures.

In TSQL Tutorial 16, l discussed and implemented the various types of user defined stored procedures. I’m going to continue the discussion and probably end my discussion about stored procedures in this tutorial with some very important things to know about stored procedures apart from just writing the query to create them. In the last tutorial, l mentioned that stored procedures are pre-compiled and the execution plan is stored in the database, so subsequent calls to the procedure do not need any compilation, the old execution plan is used for faster performance. This is because compiling the code takes  time. The Query Optimizer generates a new execution plan the first time Stored Procedure is executed after creation or altering. However,we can also force the optimizer to generate a new execution plan for the Stored Procedure. This is called recompiling. One of the main reason, for recompiling could be that we create new INDEX or alter existing indexes on the tables or views being used in our Stored Procedures. This will mean that there is a faster plan now than the one already being used. l’ll be talking about Indexes and Views which are also very important database object in the up coming tutorials. Parameters passed to stored procedures can also affect the Stored Procedure’s execution plan when stored procedures take parameters whose values differs in such a way that it causes different optimized execution plans to be created often. There are different ways to recompile a stored procedure. Lets take a look at the ways of recompiling a stored procedure in sql server using TSQL.

1.WITH RECOMPILE option(during creation time)

At creation time of a stored procedure, the WITH RECOMPILE option can be stated. That means, the stored Procedure is recompiled every time it is executed. This not commonly used as it slows down the Stored Procedure execution due to recompilation before every single execution. It’s just like running the whole statements as a batch each time. Not a good idea if you want the stored procedure to increase performance. This is the syntax for creating a stored procedure with the recompile option.

CREATE PROCEDURE <procedurename>(parameters) WITH RECOMPILE

AS   <tsql statement goes here>

2. WITH RECOMPILE option(During execution time)

We can use the recompile option during the execution time of the stored procedure so that we only recompile the stored procedure when necessary. This is better than the option of creating the stored procedure to recompile during any execution. This means, the stored procedure will be created just in the normal form without specifying the with recompile option, then it’s execution plan will be used for all subsequent executions until a call to the stored procedure specified the WITH RECOMPILE option, then the stored procedure will be recompiled and the new execution plan will be stored for all subsequent execution. Let’s see how this looks like, if we want to recompile the stored procedure during execution.

EXEC <procname> … WITH RECOMPILE

3. Using a system Stored Procedure to specify recompilation of a procedure.

There is an important in-built system stored procedure called SP_Recompile. This system procedure helps in recompiling user defined stored procedures. The syntax looks like this,

 SP_Recompile <procedurename>

It forces a recompile of the stored procedure the next time it is run after the sp_recompile statement. This means, when you put the SP_Recompile in your statements, the procedure is marked to be recompiled the next time it is called after the sp_recompile statement location. Its good to know that, the sp_recompile doesn’t immediately recompile the stored procedure but it marked it for recompilation the next time it is called after the location of the sp_recompile statement. This system stored procedure for recompiling user defined stored procedures is very useful and can be used to indicate stored procedures to be recompiled at any time.Now let’s take a look at the abilities and inabilities of Stored procedures.

Stored Procedure can have:-

1. Any kind of DDL and DML statements
2.Errorhandling (TRY —-CATCH)
3.Use all types of table(temp),table variables
4.can output multiple values using OUT parameters, can return one integer value
5.Can take any inputs
6.Set default inputs
7.Use Dynamic SQL
8.Can have nested stored procedures-call Stored Procedure from inside of another Stored Procedure and can also call functions.

Stored Procedure cannot do the following:
1.cannot output 2D DATA(cannot return/output table variable)
2.can not be called from Select Statement
(can be executed using only Execute/exec statement) and can not be called in a function

Always remember this tips:
1.Return statement in stored procedure can return only integer value
2.Once return statement is executed, execution control is return to the next statement
after exec/execute statement
3.If we want to return non-numeric values from Stored Procedure, then use output parameters(user can have any number of input and output parameters)
4.All the parameters must be passed in accordance with their definition while creating the Stored Procedure (There has to be one-to-one mapping between parameters at creation and supplied values at execution)

Main Advantages of Stored Procedures
1.Pre-compiled code hence faster
2.They allow modular programming
3.Reusability
4.They can enhance security of your application. Users can be granted permission to execute Stored Procedure without having direct permission on the objects referenced in the procedure
5.They can reduce network traffic. An operation of hundreds of lines of code can be performed through single statement. That is far better than sending hundreds of lines of code over the network.

I hope this helps someone. Questions, suggestions and feedback should be dropped in the comment section. I’ll be discussing User Defined Functions in the next tutorial. Keep in touch, Peace.