Top 18 SQL Stored Procedure Interview Questions Answers

Here are the most frequently asked Stored procedure related interview questions for fresher as well as experienced developer which are asked during SQL Server interview. This set contain almost all stored procedure related interview questions, but if you faced any new stored procedure related interview question then share with us as a comment we will be thankful and it will help other readers as well. So help each other and get job. So lets start now. If you will share any question with answers then you can win a book.
So share question and win book....

1). What is Stored Procedure? 
Ans: A stored procedure is a named group of SQL statements that have been previously created and stored in the server database. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.

2). What are the uses of stored procedure?
Ans: 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 are the type of Stored procedure in SQL Server?
Ans:

• System Defined Stored Procedure
• Extended Procedure
• User Defined Stored Procedure
• CLR Stored Procedure

click here for more detail

4). What is the difference between a user defined function and a Stored procedure?
Ans: Click here for answer

5). Explain about recursive stored procedures?
Ans: 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.

6). Can a stored procedure call itself or recursive stored procedure? How much level SP nesting is possible?
Ans: Yes. Because Transact-SQL supports recursion, you can write stored procedures that call themselves. You can nest stored procedures and managed code references up to 32 levels.

7). Have you ever created or used recursive stored procedure? Give example?
Ans: I created a recursive stored procedure for calculating the factorial of a number.

8). What are the advantages of using a Stored Procedures? 
Ans: Following are the main advantage of using a SP

• Reduce network usage between clients and servers – stored procedures perform intermediate processing on the database server reducing unnecessary data transfer across the network
• Improved security – database administrator can control the users who access the stored procedure
• Reduced development cost and increased reliability
• Stored procedures are tunable to improve the performance. When same stored procedure executed again, it can use the previously cached execution plans
• Separate or abstract server side functions from the client side
• Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients.
• Access to other database objects in a secure and uniform way
• Can prevent SQL injection attacks
• Unit testable
• Encapsulation of business logic – less chances to data become corrupted through faulty client programs.

9). What are the disadvantages of using a Stored Procedures? 
Ans: Following are the main disadvantage of using a SP

• Writing and maintaining stored procedures requires more specialized skills.
• There are no debuggers available for stored procedures
• Stored procedure language may differ from one database system to another.
• Poor exception handling
• Tightly coupled to the database system
• Not possible to use objects
• Sometimes it is hard to understand the logic written in dynamic SQL

10). How do we recompile a stored procedure at run time? 
Ans: Add the WITH RECOMPILE hint when creating or executing the stored procedure

11). How to Optimize Stored Procedure Optimization? 
Ans: There are many tips and tricks for the same. Here are few:

• Include SET NOCOUNT ON statement.
• Use schema name with object name.
• Do not use the prefix "sp_" in the stored procedure name.
• Use IF EXISTS (SELECT 1) instead of (SELECT *).
• Use the sp_executesql stored procedure instead of the EXECUTE statement.
• Try to avoid using SQL Server cursors whenever possible.
• Keep the Transaction as short as possible.
• Use TRY-Catch for error handling.

12). How you will execute the stored procedure as a different user?
Ans: I will use EXECUTE AS
Example-
13). What is the difference between stored procedure and view in SQL Server?
Ans: Views : They are the virtual table which consists of one or more rows and columns from different real tables of the Database. It is the template of rows and columns of multiple tables. You cannot pass any parameters here.

Stored Procedures : They are a collection of pre-executed sql Statements where you can send the parameters as input and retrieve the output data.

Summery difference of Stored procedure and View:
Stored Procedure:
1.Accept parameters
2.Can not be used as a building block in large query.
3.Can contain several statement like if, else, loop etc.
4.Can perform modification to one or several tables.
5.Can not be used as the target for Insert, update, delete queries.
6.We can use view inside stored procedure.

Views:
1.Does not accepts parameters
2.Can be used as a building block in large query.
3.Can contain only one single Select query.
4.Can not perform modification to any table.
5.Can be used (sometimes) as the target for Insert, update, delete queries.
6.We can't use stored procedure inside view.

14). How do we recompile a stored procedure at run time? 
Ans: By adding the WITH RECOMPILE hint when creating or executing the stored procedure.

15). Explain the differences between Stored Procedures and triggers?
Ans: 1. When you create a trigger you have to identify event and action of your trigger but when you create s.p you don't identify event and action
2.Trigger is run automatically if the event is occurred but s.p don't run automatically but you have to run it manually
3. Within a trigger you can call specific s.p but within a sp you cannot call a trigger
4.Trigger execute implicitly whereas store procedure execute via procedure call from another block.
5.We can call a stored procedure from front end (.asp files, .aspx files, .ascx files etc.) but we can't call a trigger from these files.
6. Stored procedure can take the input parameters, but we can't pass the parameters as an input to a trigger.

16). When would you use stored procedure or functions ? 
Ans: Functions are computed values and cannot perform permanent environmental changes to SQL Server (i.e. no INSERT or UPDATE statements allowed).
A Function can be used inline in SQL Statements if it returns a scalar value or can be joined upon if it returns a result set.
for more see the diffrence between them, and use according to that.

17). Why use functions instead of stored procedure in SQL?
Ans: If you want perform some calculation base on some column value, then you can use function instead of stored proc because you can not call a procedure in a select statement but you can call function in a select statement.

18). Can we use try and catch in stored procedure and function both? give and example?
Ans: We can use try and catch block in stored procedure, but not in user defined function(UDF)
Example(try catch in SP)

19). Can we use multiple select statements in a Stored Procedure SQL Server?
Ans: Yes, we can use multiple select statements in a SP.

20). Can we create Stored Procedure without "Begin" and "End" refer the below image and try to answers?

Ans: Yes, We can
21). Can we return NULL value using stored proc?

Ans: No, Stored procedures are not allowed to return the NULL value.
If you will try to return null value the you will get message as shown in the above screenshot.

Best of luck, please share interview question if you have any.