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
Stored procedures Interview questions
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.
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.
- 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.
- 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.
- “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.
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
Stored procedures 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
Redis interview questions
SQLite interview questions
RavenDB Interview Questions
Couchdb Interview Questions
Weblogic Interview Questions
MariaDB Interview Questions
Cassandra Interview Questions
PouchDB Interview Questions
Oracle DBA Interview Questions
Subscribe Our NewsLetter
Never Miss an Articles from us.
- Common Interview Questions
- Python Flask Interview Questions
- NoSQL interview questions
- JQuery Interview Questions
- C programming interview questions
- AngularJS Interview Questions
- Express and Node JS Interview Questions
- Core Java interview questions
- HTML Interview Questions
- Laravel interview questions
- Wordpress Interview Questions
- Core PHP Interview Questions