How to test stored procedure in SQL Server

In this SQL Server tutorial, we will understand How to test stored procedure in SQL Server. Additionally, we will also discuss the following topics

• How to test stored procedure in SQL Server
• How to verify stored procedure in SQL Server
• How to test performance of stored procedure in SQL Server
• How to run and test stored procedure in SQL Server
• How to check stored procedure in SQL Server
• How to check stored procedure in SQL Server with if exists
• How to check stored procedure performance in SQL Server
• How to check stored procedure history in SQL Server
• How to check running stored procedure in SQL Server
• How to check stored procedure status in SQL Server
• How to check stored procedure permissions in SQL Server
• How to check stored procedure execution time in SQL Server

How to test performance of stored procedure in SQL Server

In SQL Server, we can check and test the performance of a stored procedure by using the SQL Server Management Studio. Moreover, in SQL Server Management Studio, there are multiple options available that can help to test a stored procedure.

Let’s understand how to use these options in SQL Server Management Studio.

Using SQL Server Profiler

The SQL Server Profiler is a graphical user interface for SQL Trace that allows us to monitor a Database Engine or Analysis Services instance.

Moreover, it allows to store information about each event in a file or table for subsequent analysis. And we can use it to check the performance of a stored procedure in SQL Server.

Here are a few steps that we can follow to check the stored procedure using the Profiler.

• First, open SQL Server Management Studio and connect to your database instance.
• Next, move to the menu bar and then select Tools and click on “SQL Server Profiler“. This will run the SQL Server Profiler as a separate application.

• Once the Profiler application is started, connect to the required Database Engine.

• After this, a Trace Property page will appear where we need to specify the Trace name and select the Blank template.
• And then, we need to select some Events which are shown in the image below. And after specifying all the required options click on “Run“.

• Now, we need to execute the required stored procedure in the SQL Server Management Studio.
• And after running the execution statement, move back to the Profiler window, and see the result for the stored procedure.

Read How to use union in view SQL Server

Display Estimated Execution Plan

While using the SQL Server Management Studio for executing a stored procedure, we can also use the Display Estimated Execution Plan option.

This option will show the execution plan for the stored procedure and displays the execution of each query in the procedure.

To use this option, first, we need to execute the required stored procedure. And then, from the menu bar select Query and click on the Display Estimated Execution Plan option or use “Ctrl + L“.

After the above step, it will display the execution play in the result grid.

Now, we can also hover over each section and it will display more details related to that operation.

Also, check: Full-text search in SQL Server

How to check stored procedure status in SQL Server

In this section, we will understand how to check the execution status of a stored procedure in SQ Server.

Now, when we run a stored procedure in SQL Server by default, it produces an integer number that represents its execution status. The 0 number denotes a successful execution, while non-zero values denote an error.

Let’s understand this implementation using an example in SQL Server. For this example, we have created a stored procedure that returns records of a specified department.

USE [sqlserverguides]
GO

CREATE   PROCEDURE [dbo].[GetEmployeesByDept]
( @Dept VARCHAR(20) )
AS
BEGIN
	SELECT Name, gender, Department FROM dbo.Employees
	WHERE Department = @Dept
END
GO

Now, let’s execute the above-created stored procedure using the following query.

USE [sqlserverguides]
GO

DECLARE @return_value INT

EXEC @return_value = [dbo].[GetEmployeesByDept] 
     @Dept = 'Sales'

SELECT @return_value AS [Status Code]

In the above script, first, we have declared an integer variable. And after this, we are using that variable in the execution statement. After the execution statement, we are using the SELECT statement to get the value of the variable.

In the end, we will get the following output.

In the output, we got 2 resultsets, the first resultset is the result returned from the SELECT statement of the stored procedure. And the second resultset is the result of the SELECT statement used for the variable.

Read: SQL Server find text in stored procedure

How to check stored procedure execution time in SQL Server

Here we will understand how to check the execution time of a stored procedure in SQL Server.

Now, there are multiple ways to fetch the execution of a stored procedure. And we will illustrate the most useful methods in this section.

Using SQL Server Profiler

The SQL Server Profiler is a tracing tool provided by Microsoft. And it is used to track down and analyze events and operations performed on a certain SQL Server database engine or Analysis Service.

And we can use the SQL Server Profiler to track the execution time of a stored procedure. Here are some simple steps that we can follow.

• First, open SQL Server Management Studio and connect to your database instance.
• Next, move to the menu bar and then select Tools and click on “SQL Server Profiler“. This will run the SQL Server Profiler as a separate application.

• Once the Profiler application is started, connect to the required Database Engine.
• After this, a Trace Property page will appear where we need to specify the Trace name and select the required template.
• And then, we can also select some Events from the Events Selection page. And after specifying all the required options click on “Run“.

• Now, we need to execute the required stored procedure in the SQL Server Management Studio.
• And after running the execution statement, move back to the Profiler window, and see the result for the stored procedure.
• Now, from the Profiler results, we can see the Duration, Starting time, and End time of the execution.

Using Transact-SQL

Now, let understand how we can get the execution time of a stored procedure using a query. Now, there are multiple implementations that we can use to fetch the execution time.

Using sys.dm_exec_procedure_stats

In SQL Server, sys.dm_exec_procedure_stats is a system view that holds some statistics related to each cached stored procedure. Now, this view holds a row till a stored procedure is cached. Let’s understand how to use this view to check the execution time of a stored procedure.

First, we need to execute the required stored procedure using EXECUTE or EXEC statements. And after that, we can use the following query to get some statistics of the recently executed procedure.

SELECT DB_NAME(database_id) DatabaseName,
OBJECT_NAME(object_id) ProcedureName,
cached_time, last_execution_time, execution_count,
total_elapsed_time/execution_count AS avg_elapsed_time,
type_desc
FROM sys.dm_exec_procedure_stats
ORDER BY avg_elapsed_time;

Now, after executing the above query, we will get some data related to the executed stored procedure. And from the data, we can easily check the execution time of a stored procedure.

Using STATISTICS TIME

In SQL Server, we also use one more method to get the execution time of a stored procedure. In this method, we will understand how to use the SET STATISTICS TIME statement to fetch the execution time.

The SET STATISTICS TIME statement is used to display the number of milliseconds taken by a statement to parse, compile and execute.

Here is a simple example of how to use it to get the execution time of a stored procedure.

SET STATISTICS TIME ON
EXEC dbo.GetEmployeesByDept 'Sales'
SET STATISTICS TIME OFF

Now, when we will run the above script, we will get the following message with the result.

Read: Alter Stored Procedure in SQL Server

How to check stored procedure history in SQL Server

In this section, we will understand how to monitor and check the stored procedure history in SQL Server.

Now, the simplest way to achieve this task is by using the SQL Server Profiler.

• First, open the SQL Server Profiler, connect to the Database instance.
• After this, from the menu bar select File and click on “New Trace” or use the shortcut Ctrl + N.
• After this, a Trace Property page will appear where we need to specify the Trace name and select the SP_Counts template. And then, simply click on “Run“.
• Now, the execution of every stored procedure in the SQL Server instance will be monitored. And after working with stored procedures, we can review the history in Profiler.

Read: Rename stored procedure in SQL Server

How to check stored procedure permissions in SQL Server

In this section, we will understand how to check permission related to the stored procedures for a user in SQL Server.

In SQL Server, there are many ways to check the permissions of a stored procedure. But one of the simplest ways is to use the HAS_PERMS_BY_NAME() function in SQL Server. The HAS_PERMS_BY_NAME() is a system function that evaluates the specified permission of the current user on a securable.

Let’s understand how to use the HAS_PERMS_BY_NAME() function with the help of an example.

SELECT NAME, 
    has_perms_by_name(name, 'OBJECT', 'EXECUTE') as [Execute Permission],
	has_perms_by_name(name, 'OBJECT', 'ALTER') as [ALTER Permission],
    has_perms_by_name(name, 'OBJECT', 'VIEW DEFINITION') as [View Definition Permission]
FROM sys.procedures

Now, the above query will return permissions related to each stored procedure in the database for the current user. The query will return the following result.

As we can see in the output if the user has the specific permission then, it is represented by 1. And if the user does not have the permission then it is represented by 0.

Read: SQL Server stored procedure case statement

How to check stored procedure in SQL Server with if exists

In SQL Server, we generally use the IF EXISTS statement to check the existence of any object in the database. Similarly, we can use this statement to check the existence of a stored procedure in a database.

Now, this IF EXISTS statement is a combination of two components. The first is the IF statement, and the second is the EXISTS operator. Where the IF statement is a conditional statement and EXISTS on the other hand is used to test the existence of a row in a given subquery.

The EXISTS operator returns TRUE if a row exists in a subquery. And the IF statement is used to check the condition and perform some operations based upon that.

Now, one of the common examples of using this IF EXITS statement is for checking the existence of a stored procedure in the database. And deleting and creating a new procedure based upon its existence. Let’s understand this example using the following script in SQL Server.

USE [sqlserverguides]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'GetInfoByDept')  
DROP PROCEDURE [dbo].[GetInfoByDept]  
GO
CREATE PROCEDURE [dbo].[GetInfoByDept]
( @Dept VARCHAR(20) )
AS
BEGIN
	SELECT Name, gender, Department FROM dbo.Employees
	WHERE Department = @Dept
END
GO

In the above example, we are using the IF EXITS statement to check the existence of GetInfoByDept. Now, if the procedure already exists then, we are using the DROP statement to delete the procedure.

And if the procedure does not exist then, we are creating a new stored procedure with the same name.

Read: SQL Server stored procedure if exists update else insert

How to check running stored procedure in SQL Server

In this section, we will understand how to check for the stored procedures which are running. And we will demonstrate this method using an example.

For example, first, we are going to create a stored procedure that can run for a while in the background. So, we have used the following query to create a stored procedure.

USE [sqlserverguides]
GO

CREATE PROC [dbo].[ScheduledProc]
AS 
BEGIN
    INSERT INTO dbo.SimpleTable ( entry_datetime ) 
    VALUES(GETDATE());
END

BEGIN
    WAITFOR DELAY '00:02:00';
    EXEC ScheduledProc
END
GO

The above procedure will insert the current Datetime value into the SimpleTable after every 2 minutes. And after each insertion, the procedure will be called again for execution.

Remember, we need to stop the execution of this stored procedure manually.

So, we have created a stored procedure that will run in the background. Now, the check the running stored procedure, we will run the following query.

SELECT
  object_name(st.objectid) AS [Procedure Name],
  connect_time, last_read, last_write
FROM
  sys.dm_exec_connections AS qs 
CROSS APPLY sys.dm_exec_sql_text(qs.most_recent_sql_handle) st 
WHERE
  object_name(st.objectid) IS NOT NULL

The above query will return the names of all the stored procedures in the databases that ars running. Here is the sample output of the query.

Read: SQL Server check user permissions on table

How to run and test stored procedure in SQL Server

In SQL Server, if we want to check or test the performance of a stored procedure, we need to run it first and then, look for results.

Now, there are different ways where we first run the stored procedure and then monitor its performance. And we have already explained those methods at the starting of this article. For more details, you can refer to “How to test stored procedure performance“.

How to verify stored procedure in SQL Server

In this section, we will how to verify a stored procedure in SQL Server. Here verify means to check if the stored procedure is correct or not in terms of syntax and working. Moreover, we will talk about verifying both existing as well as a new stored procedure.

Read: Optional Parameters in SQL Server Stored Procedure

Verify while creating a stored procedure

Let’s see a few important things that we need to understand while creating a stored procedure.

• In SQL Server, while creating a stored procedure, the first in the batch should be CREATE PROCEDURE or CREATE PROC.
• In SQL Server, we can also create temporary stored procedures. We utilize the # sign as a prefix for the name of a local stored procedure and the ## syntax as a prefix for the name of a global stored procedure.
• To pass data in the stored procedure, we can use one or more parameters. Moreover, In a stored procedure, we use the @ sign before the parameter’s name to define it.
• By using the WITH RECOMPILE statement, we instruct SQL Server to compile the stored procedure whenever used. It degrades system performance, so avoid using it.
• By using the WITH ENCRYPTION statement, we can prevent the users from viewing the code of the stored procedure.
• If a stored procedure returns a value to the caller, then to handle that value, the caller must declare a variable. And for this purpose, we can use DECLARE clause.
• To validate a stored procedure, we should use the IF EXISTS function to check this existence first. Moreover, we can utilize RAISEERROR to generate an error.

Now, if we are using SQL Server Management Studio then, we can verify the syntax using the Parse option. For this, simply use the Ctrl+F5 shortcut or click on the Parse from the Manu bar.

Verify the execution of a stored procedure

After successfully creating a stored procedure, we need to verify its execution. And for this task, we can check the execution status of the stored procedure in SQL Server.

Now, we have already explained how to check the execution status of a stored procedure in this article itself.

Read: How to check if SQL Server is running

How to check stored procedure in SQL Server

Now, in this section, we will understand how to check an existing stored procedure in SQL Server. It could also help in verifying an existing stored procedure.

To check a stored procedure in SQL Server, we need to view the definition of that stored procedure. And in SQL Server, that are various ways to view the definition of a stored procedure.

But, in this section, we will only take a look at one standard method. And this method includes the use of sp_helptext.

Now, the syntax to check the stored procedure using sp_helptext is as follows.

USE database;  
GO  
EXEC sp_helptext N'database.schema.procedure_name';

Now, let’s take a look at one example of this implementation. And this example includes the use of the following query.

USE [sqlserverguides];  
GO  
EXEC sp_helptext N'sqlserverguides.dbo.AllEmployees';  

And the above query will return the definition for the AllEmployees stored procedure. This stored procedure was created in the sqlserverguides database under the dbo schema.

Read: How to get list of users in SQL Server

How to test stored procedure in SQL Server

In SQL Server, we can test a stored procedure in different ways. For example, we can test the performance of a stored procedure using the SQL Server Profiler.

Similarly, we can test the execution time of a stored procedure. And we can also test a stored procedure by checking their execution status.

Now, we have already covered a bunch of things that can help to test your stored procedure in one way or another. So, for more details, you can refer to the different sections explained in this tutorial.

You may also like to read the following SQL Server articles.

• Loop in SQL Server stored procedure
• How to delete a view in SQL Server
• SQL Server stored procedure modified date
• Try catch in SQL Server stored procedure
• Stored procedure for search functionality in SQL Server
• View SQL Server Error Logs

So, in this tutorial, we have understood How to test stored procedure in SQL Server. Additionally, we have also discussed the following topics:

• How to test stored procedure in SQL Server
• How to verify stored procedure in SQL Server
• How to test performance of stored procedure in SQL Server
• How to run and test stored procedure in SQL Server
• How to check stored procedure in SQL Server
• How to check stored procedure in SQL Server with if exists
• How to check stored procedure performance in SQL Server
• How to check stored procedure history in SQL Server
• How to check running stored procedure in SQL Server
• How to check stored procedure status in SQL Server
• How to check stored procedure permissions in SQL Server
• How to check stored procedure execution time in SQL Server