Stored Procedure Interview Questions And Answers

Download Stored Procedure Interview Questions and Answers PDF

Optimize your Stored Procedure interview preparation with our curated set of 17 questions. These questions are specifically selected to challenge and enhance your knowledge in Stored Procedure. Perfect for all proficiency levels, they are key to your interview success. Download the free PDF now to get all 17 questions and ensure you're well-prepared for your Stored Procedure interview. This resource is perfect for in-depth preparation and boosting your confidence.

17 Stored Procedure Questions and Answers:

Stored Procedure Job Interview Questions Table of Contents:

Stored Procedure Job Interview Questions and Answers

1 :: What is a stored procedure?

A stored procedure sometimes is also called as SP or sproc. This feature is available to those applications accessing relational database. Features and commands of stored procedure are actually stored in database dictionary.

2 :: What are the uses of stored procedure?

Stored procedures are often used for data validation and as access control mechanism. Logic applied in applications can be centralized and stored in applications. Complex procedures and functionalities which require huge amount of data processing and logic implementation access their data by procedures. Data is stored in these procedures and accessed by procedures.

3 :: What is the difference between a user defined function and a Stored procedure?

A user defined function and stored procedures are almost similar but there exists a difference between their implementation procedures in the code. Stored procedure needs to be invoked whereas a UDF can be used like any other statement.

4 :: Explain about the implementation of stored procedures?

Implementation of stored procedure varies for different databases and vendors. Stored procedures are very flexible and they can be implemented in variety of languages. Stored procedures written in non SQL languages may have a very little compatibility with SQL.

5 :: State the different extensions for stored procedures?

Most of the database systems have proprietary and vendor based extensions. Microsoft allows procedures to be written using Transact-SQL. Oracle calls its extension as PL/SQL. DB2 has its extension as PL/SQL. PL/pgSQL is the extension used by Postgre SQL and this allows users to have their own functional language such as pl/PHP and pl/Perl.

6 :: State about the security aspects of stored procedures?

Stored procedures should be written very carefully because they store complex and logical data. Security permissions should be very finely applied and this is possible by carefully written code. Permissions for client data should be set in such a manner that it could be accessible only by the client and the method of access should be according to the procedure laid down in the Stored procedures.

7 :: Does storing of data in stored procedures increase the access time? Explain?

Data stored in stored procedures can be retrieved much faster than the data stored in SQL database. Data can be precompiled and stored in Stored procedures. This reduces the time gap between query and compiling as the data has been precompiled and stored in the procedure. To avoid repetitive nature of the data base statement caches are used.

8 :: Explain about the difficulties faced by the database developer in implementing pre compiled statements?

There are many difficulties for implementing pre compiled statements because it should have all the arguments provided to it during compile time. It also depends upon the database and configuration. Performance also varies and it largely depends upon whether it is a generic query or user defined functions.

9 :: Explain about the implementation of business logic in Stored procedures?

Stored procedures implement business logic into the database. It is embedded as API and this reduces the implementation of Logic code again explicitly. Implementation of business logic internally reduces the chances of data becoming corrupt.

10 :: Explain the benefits of running stored procedure on a database engine?

Stored procedures can run directly run on a data base engine. In industries where automation is the key a stored procedure can run entirely on the data base provided to it and this runs on a specialized data base server. Network communication can be avoided. Also this procedure is useful for execution of complex SQL statements.

11 :: Explain about recursive stored procedures?

Recursive stored procedures are used for performing repetitive tasks. Recursive feature is disabled by default but can be activated by using the following command on the server max_sp_recursion_depth, also don’t forget to rename the system variable to a non zero variable.

12 :: Explain about the process which takes place to execute a Stored routine?

CREATE PROCEDURE and CREATE FUNCTION statement are used to create stored routine. It can act as a function or a procedure. A procedure can be called by using a call statement and pass output with the help of output variables. It can call other Stored routines and it can be called from the inside of a statement.

13 :: Explain about the RETURN statement?

A RETURN statement is used to terminate the execution of a stored function. This inturn returns a value of expr to the function caller. In a stored function there should be at least a single return statement. If there exists multiple functions it can have multiple exit points.

14 :: Explain about cursors?

Cursors are supported by procedures, functions and triggers. Syntax of the cursors is embedded in SQL. Cursor should be declared before declaring handles. Before declaring cursors it is imperative to declare variables and conditions.

15 :: Explain about the properties of Cursors?

These are the properties which a cursor has Assensitive, Read-only, Non-Scrollable. Assensitive is used either to perform a copy or not to perform a copy. When Read only function is used it is not updatable. It can scroll only in one direction and it cannot skip tables.

16 :: How do you notify DBPAK about Stored Procedure?

There are three following statements to execute a DBPAK in the stored procedure. These are the following steps.
1) In the browser hierarchy double click on the Query widget
2) After clicking it try finding insertProc resource in the Resource editor
3) Edit the insertProc resource.

17 :: Explain about the case statement present in Stored procedure?

CASE statement contains a complex conditional construct. If a certain statement is evaluated as true then the search_condition makes sure that the following statement gets executed. If there are no true statements are present then the ELSE clause is executed. An error is generated if there is no statement.