Stored Procedures Interview Questions

The group of SQL statements which are or have been stored in the server database is known as a stored procedure. Input parameters are accepted in the stored procedures so that several clients can use that single procedure over the network using that single input data. It holds an advantage that whenever the procedure gets updated; all the clients automatically get the updated version. Stored procedures help in reducing the network traffic and also improve the performance of the data. The integrity of data can be ensured while making use of stored procedures.

Stored procedures are used for various different things. Some of the uses of stored procedures are as follows:

• Stored procedures are often used as access control mechanism and for data validation.
• The logic applied in this application is centralized and is stored in the application.
• Procedures are used to process the huge amount of data for complex procedures and functionalities and for logic implementation access of their data.
• These procedures are used to store data in them and can be accessed by procedures.
• They support modular programming.
• Faster execution takes place.
• It can also be used as a security mechanism.
• They are used for reducing network traffic.

The types of stored procedures present in an SQL server are listed as follows: –

• User-defined stored procedures– these are the modules or routines which encapsulate code for reuse. There are 2 types of user-defined stored procedures:
• Transact-SQL: It is a collection of transaction SQL statements which takes and returns user-specified parameters.
• CLR: It is a reference to the Microsoft .NET framework.
• Extended stored procedures– with the help of these procedures, you can create your own external routine in a programming language.
• System stored procedures– these are used to perform various administrative activities in the SQL server.

The following are the advantages of using a stored procedure: –

• It reduces the network usage between the client and the server and an immediate processing is performed on the database server. It hence reduces the unnecessary data transfer.
• Security is improved and the user access to the stored procedure is managed by the administrator.
• Development cost reduces and reliability increases.
• It improves the performance of the database.
• These are used to encapsulate the logic and hence the code can be changed without affecting the clients.
• The access to other database objects becomes more secure.
• SQL injection attacks can be avoided using this.

Everything which has advantages has disadvantages too. The disadvantages of using stored procedures are listed below:

• Specialized skills are required for writing and maintaining the code of a stored procedure.
• Debuggers are not available for a stored procedure.
• The language in which a stored procedure is written may differ from one database to the other.
• Exception handling in a stored procedure is not up to the mark.
• These are tightly coupled to the database.
• You may not be able to use objects.
• Sometimes the logic may not be understood by the programmers.

The various tips and tricks for optimising a stored procedure optimization are listed below:

1. “SET NOCOUNT ON” statement should be included.
2. A schema with its object name should be used.
3. The prefix name “sp_” should not be used in a stored procedure name.
4. Instead of using (SELECT*), use IF EXISTS (SELECT 1).
5. SQL server cursors should be avoided whenever possible.
6. The transaction should be kept as short as possible.
7. In case of error handling, make use of the try-catch block.

The differences between stored procedure and view in SQL server are as follows:

• A stored procedure accepts parameters. Whereas, views do not accept parameters.
• In any large query, a stored procedure cannot be used as a building block. Whereas, a view can be used as a building block.
• A stored procedure can contain several statements. Whereas, a view can contain only one single select query.
• Using stored procedures, one or more tables can be modified. Whereas, using view no table can be modified.
• A view can be used within a stored procedure. Whereas, a stored procedure cannot be used inside a view.

Following are some differences between stored procedures and triggers:

• Event and actions needs to be identified at the time of creating a trigger. Whereas, at the time of creating a stored procedure, it is not important.
• Trigger can be run automatically when any event occurs. Whereas, stored procedures have to be run manually.
• A stored procedure can be called within a trigger. Whereas, a trigger cannot be called in a stored procedure.
• Triggers execute implicitly. Whereas, stored procedures execute explicitly.
• A trigger cannot be called from front end. Whereas, a stored procedure can be.

Repetitive tasks are being performed by the recursive stored procedures. By default this function is disabled but can be activated using a particular command. The command is as follows-

Max_sp_recursion_depth;
After using this, the system variable should be renamed to a non-zero variable.

As we all know, functions are the computed values and they cannot perform any permanent environmental changes to the SQL server. Neither insertion in the statement is allowed nor can it be updated. If a function returns a scalar value or can be joined upon if it returns a scalar set, then it can be used inline in the SQL statements. These are used when the logic applied in the application is centralized and the data is stored in the application. These stored procedures are used when we need to validate the data and the complex procedures.