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. We will be starting from the very basic SQL Server Stored Procedure Interview Questions to the advanced level of questions.
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 the 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 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 gets 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
- Input parameters
- 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 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.
- Right Click and select ‘Execute Stored Procedure
- If the procedure, expects parameters, provide the values and click OK
- 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
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
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 parameters.
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.
- We cannot return more than one value using the return status variable in SQL Server.
- 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, 1st it checks the syntax of that query, 2nd it compiles that query, 3rd 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.
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.
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
- The With Encryption
- With Recompile
The 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 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.
The With Recompiled Attribute:
- 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.
- 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).
- 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.
- Forcing a procedure for recompilation and prepared a query plan is required when the database undergoes significant changes to its data or structure.
- 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
- Private / Local Temporary Stored Procedure
- Public / Global Temporary Stored Procedure.
What are Private / Local Temporary Stored Procedure?
- These are created with the # prefix added to the procedure name.
- Private or Local Temporary Stored Procedures are executed by the connection that created it.
- These are automatically deleted when the connection created it 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 Public / Global Temporary Stored Procedure?
- These are created with the ## prefix added to the procedure name.
- Any connection can execute the global temporary stored procedure.
- 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.
- 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.
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 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 as deferred name resolution in 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. In the next article, I am going to discuss SQL Server Joins Interview Questions and answers.