Find Top 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
Interview questions on stored procedures
- 1) What do you understand by a stored procedure?
- 2) Mention the uses of stored procedures.
- 3) What are the types of stored procedures in an SQL server?
- 4) What are the advantages of using a stored procedure?
- 5) What are the disadvantages of using the stored procedures?
- 6) How will you optimize a stored procedure optimization?
- 7) What are the differences between stored procedure and view in SQL server?
- 8) List some major differences between triggers and stored procedures?
- 9) What do you understand by recursive stored procedures?
- 10) When would you use the stored procedures or functions?
Below are the list of Best Stored procedures Interview questions and Answers
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.
- “SET NOCOUNT ON” statement should be included.
- A schema with its object name should be used.
- The prefix name “sp_” should not be used in a stored procedure name.
- Instead of using (SELECT*), use IF EXISTS (SELECT 1).
- SQL server cursors should be avoided whenever possible.
- The transaction should be kept as short as possible.
- 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-
After using this, the system variable should be renamed to a non-zero variable.
Related Interview Questions
DB2 Interview Questions
DBMS Interview Questions
Mysql Interview Questions
Mssql Interview Questions
MongoDB Interview Questions
Neo4j interview questions
NoSQL interview questions
Oracle Interview Questions
OrientDB interview questions
PL/SQL Interview Questions
Postgresql Interview Questions
SQLite interview questions
RavenDB Interview Questions
Couchdb Interview Questions
Weblogic Interview Questions
MariaDB Interview Questions
Cassandra Interview Questions
PouchDB Interview Questions
RDBMS Interview Questions
SQL Server Interview Questions
Subscribe Our NewsLetter
Never Miss an Articles from us.