Interview Questions on 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.

• 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.

• 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.

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.

• 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.

• 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.