SQL Server Stored Procedure Interview Questions

Back to: Dot Net Interview Questions and Answers

SQL Server Stored Procedure Interview Questions and Answers

In this article, I am going to discuss the most frequently asked SQL Server Stored Procedure Interview Questions and Answers. Please read our previous article where we discussed the SQL Server Exception Handling Interview Questions and Answers. If you go through the entire article, then definitely at the end of this article, you will be in a position to answers almost all SQL Server Stored Procedure Interview Questions. As part of this article, we are going to discuss the following SQL Server Stored Procedure Interview Questions with Answers.

1. What is a SubProgram in SQL Server?
2. What is a stored procedure or procedure in SQL Server?
3. Why we need the stored procedure?
4. What is the output parameter?
5. What are the stored procedure status variables?
6. Disadvantages of Return status value.
7. What are the advantages of using a stored procedure in SQL Server?
8. What is an execution plan?
9. When would you use it?
10. How would you view the execution plan?
11. How to view the text of the stored procedure?
12. How to encrypt the text of a stored procedure?
13. What are the different procedure attributes in SQL Server?
14. What is the temporary stored procedure?
15. What are Private / Local Temporary Stored Procedure?
16. What is the Public / Global Temporary Stored Procedure?
17. What is the use of a Temporary Stored Procedure?
18. What is deferred name resolution in SQL Server?

19. Can a stored procedure call itself or recursive stored procedure? How many level SP nesting possible?

What is SubProgram?

A subprogram in SQL Server is a named block of code that is directly saved on the database server. Then we can execute this subprogram when and where it is required. Under the databases, a subprogram is reported as a “stored procedure” or “stored function” or “database triggers”.

What is a stored procedure or procedure in SQL Server?

In a database management system (DBMS), a stored procedure is a precompiled set of Structured Query Language (SQL) statements with an assigned name and directly saved in the database. 

The stored procedure in SQL Server can accept both input and output parameters so that a single stored procedure can be used by several clients over the network by using different input data. The stored procedure will reduce network traffic and increase performance. If we modify the body of the stored procedure then all the clients who are using the stored procedure will get the updated stored procedure.

Why we need the stored procedure?

This is one of the most frequently asked SQL Server Stored Procedure Interview Questions in the SQL Server Interview. So let’s discuss this question in details

Whenever we want to execute a SQL query from an application the SQL query (statement) what we send from an application will be first compiled(parsed) for execution, where the process of compiling(parsing) is time-consuming because compilation occurs each time when we execute the query or statements.

To overcome the above problem, we write SQL statements or query under the stored procedure and execute because a stored procedure is a pre-compiled block of code, without compiling(parsing) the statements get executed whenever the procedures are called which can increase the performance of database server which ultimately increases the performance of the application.

If we have a situation where we write the same query again and again, we can save that specific query as a stored procedure and call it’s just by its name whenever required that query to execute.

What is the output parameter?

The Parameters of a stored procedure can be of two types

1. Input parameters
2. Output parameters

The Input parameters are basically used for bringing value into the procedure for execution whereas the output parameters are used for carrying a value out of the procedure after execution.

If a parameter is declared as output, we only require assigning a value to the parameter inside the procedure so that the procedure will send that value at the end of procedure execution.

What are the stored procedure status variables?

Whenever we execute a stored procedure in SQL Server, it always returns an integer status variable indicating the status, usually, zero indicates success, and non-zero indicates the failure. To see this yourself, execute any stored procedure from the object explorer, in SQL Server management studio.

1. Right Click and select ‘Execute Stored Procedure
2. If the procedure, expects parameters, provide the values and click OK
3. Along with the result that you expect, the stored procedure also returns a Return Value = 0

So, from this point, we understood that, when a stored procedure is executed, it returns an integer status variable. With this in mind, let’s understand the difference between the output parameters and RETURN values in SQL Server stored procedure.

We are going to use the following Employee table to understand the Stored Procedure Output Parameters and Return values in SQL Server.

The following procedure returns the total number of employees from the Employee table, using the output parameter i.e. @TotalCount.

Create Procedure spGetTotalCountOfEmployees1
    @TotalCount int output
As
Begin
    Select @TotalCount = COUNT(ID) 
    From tblEmployee
End

Executing spGetTotalCountOfEmployees1 returns 6.

Declare @TotalEmployees int
Execute spGetTotalCountOfEmployees @TotalEmployees Output
Select @TotalEmployees

Re-written stored procedure using return variables

Create Procedure spGetTotalCountOfEmployees2
As
Begin
  return (Select COUNT(ID) 
  From Employees)
End

Executing spGetTotalCountOfEmployees2 returns 6.

Declare @TotalEmployees int
Execute @TotalEmployees = spGetTotalCountOfEmployees2
Select @TotalEmployees

So, we are able to achieve what we want using output parameters as well as return values. Now let’s look at an example where return status variables cannot be used, but Output parameters can be used.

In this stored procedure, we are retrieving the name of the employee based on their Id using the output parameter i.e. @Name.

Create Procedure spGetNameById1
  @Id int,
  @Name nvarchar(20) Output
As
Begin
  Select @Name = Name 
  From tblEmployee 
  Where Id = @Id
End

Executing the spGetNameById1 procedure will print the name of the employee

Declare @EmployeeName nvarchar(20)
Execute spGetNameById1 3, @EmployeeName out
Print ‘Employee name:  ‘ + @EmployeeName

Now let’s try to do the same thing using the return status variables.

Create Procedure spGetNameById2
  @Id int
As
Begin
  Return (Select Name 
  From tblEmployee 
  Where Id = @Id)
End

When we execute the spGetNameById2 stored procedure, it will return an error stating ‘Conversion failed when converting the nvarchar value ‘Sam’ to data type int.’. The return status variable is an integer, and hence when we select the name of an employee and try to return that we get the above conversion error. 

Declare @EmployeeName nvarchar(20)
Execute @EmployeeName = spGetNameById2 1
Print ‘Employee name: ‘ + @EmployeeName

So using return values we can only return integers and that too, only one integer. It is not possible, to return more than one value using return values, whereas output parameters, can return any data type and a stored procedure can have more than one output parameter.

In general, RETURN values are used to indicate the success or failure of the stored procedure, especially when we are dealing with nested stored procedures. The return value of 0, indicates success, and any nonzero value indicates failure.

Explain the Disadvantages of Return status value.

1. We cannot return more than one value using the return status variable in SQL Server.
2. It is not possible to return values other than an integer using the return status variable in SQL Server Stored Procedure.

We can overcome the above two problems using the output variables in SQL Server Stored Procedure.

What are the advantages of using a stored procedure in SQL Server?

This SQL Server Stored Procedure Interview Questions asked in almost all interviews. The following are the advantages of using Stored Procedures over inline SQL queries in SQL Server.

Execution Plan Retention and Reusability

As there is no unnecessary compilation of queries this will reduces the burden on database (when we send a query to a SQL Server three things happen in order, 1^st it checks the syntax of that query, 2^nd it compiles that query, 3^rd it generates an execution plan) as response User will get a quick response. Let’s get into more details.

The Stored Procedures are pre-compiled and their execution plan is cached and used again when the same stored procedure is executed again. Although ad-hoc queries also create and reuse plan, the plan is reused only when the query is the textual match and the datatypes are matching with the previous call. Any changes in the datatype or you have an extra space in the query then, a new plan is created.

Reduces the Network Traffic

The Stored Procedure reduces network traffic. When we execute a stored procedure we need to send the procedure name and parameters so only these things are passed on the network but if we are not using the stored procedure then we need to write the ad-hoc queries and we need to execute them which may contain many numbers of lines. So the stored procedure reduces the network traffic as a result performance of the application increase.

Code Reusability and Better Maintainability

Multiple applications can use the same stored procedure. The different applications which want similar kind of data then they can use the same stored procedure. The advantage is that if we want to change the stored procedure then we need to change it in one place that will affect to all the application that uses it whereas if it is inline SQL query and if we have to use it in multiple applications, then we end up with multiple copies of the same inline SQL query, and if the logic has to change, then we have to change the logic at all the places, which makes it harder maintaining inline SQL. So, the stored procedure provides code reusability and maintainability.

Better Security 

By granting permission to the underlying database the user can do everything. He can view all the records as well as he can also modify the records. But if we want to restrict the user only to view the records then we need to grant only for that stored procedure which will display the records. In that way, we achieve better security with a stored procedure.

Using a stored procedure we can also avoid the SQL Injection attack.

What is an execution plan?

An execution plan is nothing but for the query to execute, what are the best possible ways available and it will choose the best possible way based on the indexes that available on the SQL Server to help that query. Based on those it generates the execution plan and then it executes the query. It will increase the performance of the application.

What is an execution plan? When would you use it? How would you view the execution plan?

An execution plan is basically a roadmap that graphically or textually shows the data retrieval methods chosen by the SQL Server query optimizer for a stored procedure or ad-hoc query and is a very useful tool for a developer to understand the performance characteristics of a query or stored procedure since the plan is the one that SQL Server will place in its cache and use to execute the stored procedure or query. From within Query Analyser is an option called “Show Execution Plan” (located on the Query drop-down menu). If this option is turned on it will display the query execution plan in the separate window when the query is run again.

How to view the text of the stored procedure?

Use system stored procedure sp_helptext <user defined stored procedure name>

Ex: sp_helptext spGetEmployeeByGenderAndDepartment

Right-click on the stored procedure in object explorer => Script procedure as =>Create To => new query editor window

How to encrypt the text of a stored procedure?

To encrypt the text of a stored procedure we use WITH ENCRYPTION option. For example

CREATE PROCEDURE SPWELCOME
WITH ENCRYPTION
AS
BEGIN
     PRINT 'WELCOME TO PROCEDURE'
END

Now it is not possible to view the text of this encrypted procedure.

What are the different procedure attributes in SQL Server?

This is one of the frequently asked SQL Server Stored Procedure Interview Questions. There are two types of attributes

1. The With Encryption
2. With Recompile

With Encryption Attribute:

If this attribute is used on the procedure the text of this procedure is encrypted and will not be shown in the text column of the syscomments table so no one will be having an option to view the content of it.

Note: When an application is developed for a client at the time of installing this application on the client system we will be using the encryption option on all the views, procedures, functions, triggers, etc. and install on the client machine. So that they will not have the chance of viewing the source code or altering the source code.

With Recompiled Attribute:

1. Whenever a procedure is compiled for the first time it prepares the best query plan according to the current state of the database and executes the query plan when the procedure is called.
2. The compilation of the procedure and preparing a query plan is prepared not only at the time of procedure creation but each and every time the server is restarted (Implicitly occurs).
3. If the procedure is created by using with Recompile procedure attribute, it is forced to be compiled each time it is executed and whenever it compiles it prepares the query plan.
4. Forcing a procedure for recompilation and prepared a query plan is required when the database undergoes significant changes to its data or structure.
5. Another reason to force a procedure to recompile is if at all the tables is added with new indexes from which the procedure might be benefited forcing for recompilation is very important because we cannot wait until the server is restarted for preparing a new query plan.

Note: Even if the With Recompile option is available it is not suggested to be used if at all there are no significant changes in the structure of the databases.

What is the temporary stored procedure?

The stored procedures which are created temporarily in a database (not stored permanently) is called as the temporary stored procedure.

There are two types of temporary stored procedures such as

1. Private / Local Temporary Stored Procedure
2. Public / Global Temporary Stored Procedure.

What are Private / Local Temporary Stored Procedure?

1. These are created with the # prefix added to the procedure name.
2. Private or Local Temporary Stored Procedures are executed by the connection that created it.
3. These are automatically deleted when the connection created is closed.

Syntax: The syntax for creating a local temporary procedure

CREATE PROCEDURE #<PROCEDURE NAME>
AS
BEGIN
  <PROCEDURE BODY / STATEMENTS / QUERIES>
END

Example: Create a Local Temporary stored procedure.

CREATE PROCEDURE #DelRec
AS
BEGIN
  Delete from EMPLOYEE where Eid = 105
END

This procedure is executed on the session which is created it and once the session is closed this procedure is automatically deleted. And we cannot access this procedure once the session is closed also we cannot access this procedure from another session.

What is the Public / Global Temporary Stored Procedure?

1. These are created with the ## prefix added to the procedure name.
2. Any connection can execute the global temporary stored procedure.
3. A Global Temporary Stored Procedure exists until the connection used by the user who created the procedure is closed and any currently executing versions of the procedure by any other connections are implemented.
4. Once the connection that was used to create the procedure is closed, no further execution of the Global Temporary Stored Procedure is allowed. Only those connections that have already started executing the stored procedure are allowed to complete.

Syntax: The syntax for creating a Global Temporary Procedure

CREATE PROCEDURE ##<PROCEDURE NAME>
AS
BEGIN
  <PROCEDURE BODY / STATEMENTS / QUERIES>
END

Example: Create a Local Temporary stored procedure.

CREATE PROCEDURE ##DelRec
AS
BEGIN
  Delete from EMPLOYEE where Eid = 105
END

What is the use of a Temporary Stored Procedure?

Temporary Stored Procedures are useful when connecting to earlier versions of SQL Server that do not support the reuse of execution plans for Transact-SQL statements or batches.

Example: Procedure with a default value.

CREATE PROCEDURE PROC3(@X INT= 100, @Y INT)
AS
BEGIN
  DECLARE @Z INT
  SET @Z=@X+@Y
  PRINT'The SUM of the 2 Numbers is: '+CAST(@Z AS VARCHAR)
END

Executing the above procedure:

1. EXEC PROC3 200, 25
2. EXEC PROC3 @X=200, @Y=25
3. EXEC PROC3 @X=DEFAULT, @Y=25
4. EXEC PROC3 @Y=25

In the 3rd and 4th case, it uses the default value of 100 to the variable X which has been given while creating the procedure.

What is deferred name resolution in SQL Server?

This is one of the most frequently asked SQL Server Stored Procedure Interview Question in SQL Server Interview. Let me explain deferred name resolution with an example. Consider the stored procedure shown below.

Create procedure spGetCustomers
As
Begin
  Select * from Customers
End

Customers table does not exist. When we execute the above SQL code, the stored procedure spGetCustomers will be successfully created without errors. But when you try to call or execute the stored procedure using Execute spGetCustomers, we will get a runtime error stating Invalid object name ‘Customers’.

So, at the time of creating stored procedures, only the syntax of the SQL code is checked. The objects used in the stored procedure are not checked for their existence. Only when we try to run the procedure, the existence of the objects is checked. So, the process of postponing, the checking of the physical existence of the objects until runtime, is called deferred name resolution in the SQL Server.

Functions in SQL server does not support deferred name resolution. If we try to create an inline table-valued function as shown below, we get an error stating Invalid object name ‘Customers’ at the time of the creation of the function itself.

Create function fnGetCustomers()
returns table
as
return Select * from Customers

So, this proves that stored procedures support deferred name resolution, whereas function does not. In fact, this is one of the major differences between functions and stored procedures in SQL Server.

Can a stored procedure call itself or recursive stored procedure? How many level SP nesting possible?

Yes. Because Transact-SQL supports recursion, you can write stored procedures that call themselves. Recursion can be defined as a method of problem solving wherein the solution is arrived at by repetitively applying it to subsets of the problem. A common application of recursive logic is to perform numeric computations that lend themselves to repetitive evaluation by the same processing steps. Stored procedures are nested when one stored procedure calls another or executes managed code by referencing a CLR routine, type, or aggregate. You can nest stored procedures and managed code references up to 32 levels.

In the next article, I am going to discuss SQL Server Joins Interview Questions and answers. Here, in this article, I try to explain the most frequently asked SQL Server Stored Procedure Interview Questions and Answers. I hope you enjoy this SQL Server Stored Procedure Interview Questions and Answers article. I would like to have your feedback. Please post your feedback, question, or comments about this SQL Server Stored Procedure Interview Questions and Answers article.