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.
CREATE PROCEDURE [dbo].[Factorial_ap]
( @Number Integer,@RetVal Integer OUTPUT )

AS
    DECLARE @In Integer
    DECLARE @Out Integer
    IF @Number != 1
        BEGIN
        SELECT @In = @Number 1
        EXEC Factorial_ap @In, @Out OUTPUT
        SELECT @RetVal = @Number * @Out
    END
        ELSE
            BEGIN
                SELECT @RetVal = 1
            END
RETURN
GO

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-
EXECUTE AS user = 'special_user'
EXECUTE YourProcerdure

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)
CREATE PROCEDURE USP_TryCatch_Test
AS
BEGIN TRY
    SELECT 1/0
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber
     ,ERROR_SEVERITY() AS ErrorSeverity
     ,ERROR_STATE() AS ErrorState
     ,ERROR_PROCEDURE() AS ErrorProcedure
     ,ERROR_LINE() AS ErrorLine
     ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH

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.

Comments

  1. It's really to good,,,thanks and regard's Ahmad

    ReplyDelete
  2. Thanks All for your kind comments..

    ReplyDelete
  3. We can return Null by Proc

    create proc nullreturn
    as begin
    select Null
    End

    exec nullreturn


    Regards
    Ashish Jain

    ReplyDelete
    Replies
    1. Hi Ashish,
      There is a difference between "Return" and Select, you can select any no of null values but you can't use Return NULL keyword in proc.

      Delete
  4. thanks its good for the knowledge

    ReplyDelete
  5. very good information. thanks.

    ReplyDelete
  6. Good Info..But which SQL language it is?
    I mean PL/SQL or Transact SQL?

    ReplyDelete
  7. Very good info, but we cant copy text and download, how can i download??

    ReplyDelete
  8. Really useful. Thank you very much

    ReplyDelete
  9. Write a procedure that takes 2 parameters, a) Table Name and b)No. of rows.

    The procedure is supposed to split the table record into no of files with each file having the no of records equal to the no. of rows provided by user.

    ReplyDelete

Archive

Contact Form

Send