How to execute stored procedure in SQL Server

In this SQL Server tutorial, we will learn How to execute stored procedure in SQL Server. We will also discuss different examples related to the execution of stored procedures in SQL Server. Here is the complete list of topics that we are going to discuss.

• How to execute stored procedure in SQL Serer
• How to execute stored procedures automatically in SQL Server
• Execute stored procedure with parameters in SQL Server Management Studio
• How to execute stored procedure without parameters in SQL Server
• How to execute stored procedure with parameters in SQL Server
• Execute stored procedure with multiple parameters
• How to execute stored procedure in sql server with input parameters
• How to execute stored procedure in sql server with output parameters
• Execute stored procedure with input and output parameters in SQL Server
• How to execute stored procedure in select statement SQL Server
• Execute stored procedure within stored procedure with parameters
• How to pass dynamic parameters to stored procedure in SQL Server
• Execute stored procedure with select statement as input parameters
• Execute stored procedure with date parameters in SQL Server
• Execute stored procedure with table-valued parameters in SQL Server
• How to execute stored procedure in SQL Server with return value

Note:- Before starting this topic, it is recommended to know what are stored procedures, and how we can create a stored procedure in SQL Server. For more details, you can also refer to the following articles.

• What is a stored procedure in SQL Server
• How to create stored procedure with examples

How to execute stored procedure in SQL Server

In SQL Server, there are 2 different approaches available to execute a stored procedure. The first approach is simply by calling the stored procedure whenever we are required to execute a procedure. And the second approach is by automatically executing the stored procedure whenever we start the SQL Server instance.

This section will discuss the manual approach of calling the stored procedure and try to demonstrate some examples.

We can execute a stored procedure in 2 ways, first is by executing a Transact-SQL query for execution. And the second way is by using SQL Server Management Studio.

Using Transact-SQL

Let’s start with the first way of manually call the stored procedure for execution. We have to use the EXECUTE or EXEC keyword to run the required stored procedure in this approach.

Additionally, it is recommended to specify the schema with the procedure name. Because the Database Engine does not have to search numerous schemas, this technique provides a minor performance benefit. And if a database has procedures with the same name in many schemas, it also prevents the undesirable procedure from being executed.

Here is the general syntax of executing the stored procedure using the EXEC keyword.

USE database_name
GO
EXEC dbo.procedure_name

---------------OR--------------------------

EXEC database_name.dbo.procedure_name
GO

Let’s understand this demonstration, with the help of an example. And in the example, we will execute a simple stored procedure with the name “GetAllRecords” and it has the following code.

USE [sqlserverguides]
GO

CREATE PROCEDURE [dbo].[GetAllRecords]
AS
BEGIN
  SELECT [id], [first_name], [gender] FROM dbo.SampleTable;
END
GO

Now, let’s try to execute this procedure with the help of the given syntax.

USE sqlserverguides
GO

EXEC dbo.GetAllRecords

In the code, first, we have specified the database name which is sqlserverguides. After this, we are using the EXEC keyword to execute the procedure which is having the schema dbo. And after execution, we will get the following result.

Using SQL Server Management Studio

Now, we will understand how to use the SQL Server Management Studio for executing a stored procedure. For this, we have to follow the following steps given below.

• First, start the SQL Server Management Studio and connect to the Database Engine.
• Now, in Object Explorer, expand the database that contains the stored procedure.
• Next, expand the Programmability directory and then under Programmability, expand Stored Procedures.
• Now, right-click the required stored procedure and click on Execute Stored Procedure. It will open a Execute Procedure dialog box.

• Now, in the Execute Procedure dialog box, specify the required parameter values and click on OK.

Read: Loop in SQL Server stored procedure

How to execute stored procedures automatically in SQL Server

Whenever we mark a procedure to be executed automatically, the procedure gets executed whenever SQL Server starts. So, before creating a procedure for automatic execution, let’s understand the need of executing a procedure automatically.

• Executing procedure automatically can be useful in implementing the database maintenance task.
• Another application for automatic execution is to create procedures that conduct system or maintenance activities in tempdb, such as building a global temporary table.

Next, let’s understand the limitations of executing a stored procedure automatically.

• The stored procedures for automatic execution can only be created in the master database by the system administrator.
• A stored procedure for automatic execution cannot have any input or output parameters.
• The stored procedures for automatic execution should not return any resultset as there is no place for resultset to show.

Now, let’s understand how we can mark a stored procedure for automatic execution. So, for this implementation, we use the sp_procoption system procedure. Here is the general syntax that we can use to mark a stored procedure for automatic execution.

USE database_name;  
GO  
EXEC sp_procoption @ProcName = '<procedure name>'   
    , @OptionName = 'startup'   
    , @OptionValue = 'on';  

Let’s understand this implementation with the help of an example. And for example, we have created a simple stored procedure that creates a global temporary table and inserts 3 records in it. It has the following code.

USE master
GO

CREATE PROC sp_GlobalTmpTable  
AS  
BEGIN  
Create Table ##MyDetails(Id int, Name nvarchar(30))  
  
Insert into ##MyDetails Values(1, 'Jack')  
Insert into ##MyDetails Values(2, 'Kane')  
Insert into ##MyDetails Values(3, 'Smith')  
 
End 

Now, let’s use the given syntax to mark the procedure for automatic execution.

USE master;  
GO  
EXEC sp_procoption @ProcName = 'sp_GlobalTmpTable'   
    , @OptionName = 'startup'   
    , @OptionValue = 'on';  

Now, whenever we start the SQL Server instance again then, we can see a global temporary table will be created automatically.

Read: SQL Server stored procedure modified date

How to execute stored procedure with parameters in SQL Server

The true power of stored procedures lies in their capacity to pass parameters and have the stored procedure handle a variety of requests. In SQL Server, parameters are used to exchange data between the stored procedure and the application that calls the stored procedure.

In SQL Server, there are 2 types of parameters associated with stored procedures.

1. Input paramaters
2. Output parameters

In this section, we will discuss both the types of parameters and we will also illustrate some examples.

How to execute stored procedure in sql server with input parameters

First, let’s understand what are input parameters and how we can execute a stored procedure with input parameters.

The input parameter in a stored procedure allows the user or caller to pass a data value to the stored procedure. And then, we can use that data value within the stored procedure for some operation.

Now, before understanding how to execute a stored procedure with input parameters, one should know how to create input parameters. For demonstration, we have created a stored procedure that accepts 3 input parameters and inserts those parameter values into the sales table. The script for the stored procedure is given below.

USE [sqlserverguides]
GO

CREATE PROC [dbo].[InsertRecords]
( @name VARCHAR(50), @joining_date Date, @sales INT )
AS
BEGIN
  INSERT INTO dbo.SalesTable
  VALUES(@name, @joining_date, @sales)
END
GO

Now to execute the procedure with input parameters, we need to specify the values for that parameters while running the execute statement. And there are 2 ways to pass the parameter values both the ways are demonstrated in the syntax below.

USE databse_name
GO


EXEC procedure_name @Param_1=value_1,
                    @Param_2=value_2

---------------------------OR----------------------------
USE databse_name
GO

EXEC procedure_name value_1, value_2

Now there are 2 approaches that we can use to specify the input parameter. The first approach is to specify the parameter name with its value, and the second approach is to directly specify the parameter values.

The working example of both the executions is illustrated in the image below.

How to execute stored procedure in sql server with output parameters

The output parameter in a stored procedure allows passing the data values from the stored procedure back to the caller or user. And then, we can use that data values outside the stored procedure for some operation.

Now, before understanding how to execute a stored procedure with output parameters, one should know how to create output parameters. For demonstration, we have created a stored procedure with 1 output parameter. And we will use this output parameter to get the total count of products available in the product table. The script for this stored procedure is given below.

USE [sqlserverguides]
GO

CREATE PROC [dbo].[usp_GetProductCount]
( @count INT OUT)
AS
BEGIN
  SELECT @count = COUNT(product_name) FROM dbo.ProductTable
END
GO

Now to execute a stored procedure with output parameter, first, we have to declare a variable that can store the value of the output parameter. After this, we can use the declared variable to get the value of the output parameter.

Here is an example of how we can execute the procedure with an output parameter.

USE [sqlserverguides]
GO

DECLARE @P_count int

EXEC [dbo].[usp_GetProductCount] @count=@P_count OUTPUT

SELECT @P_count AS [Product Count]

In this example, first, we have declared an integer variable with the name @P_count. After this, we are using execute the statement and in the statement, we have assigned the variable as a value to the output parameter. In the end, we are using the SELECT statement to get the value of the variable.

The complete execution is shown in the image below.

Execute stored procedure with input and output parameters in SQL Server

Till now, we have discussed how we can execute stored procedures with input as well as output parameters. And we have also discussed their examples separately. Now, let’s understand how we can execute a stored procedure with both input and output parameters together.

For understanding this implementation, let’s execute a simple example of a stored procedure that has both input and output parameters.

For the example, we have created a stored procedure that accepts an integer value as an input parameter. And then, we will calculate the cube of that integer value and return it to the user or caller using the output parameter. The code of the stored procedure is as follows.

USE [sqlserverguides]
GO

CREATE PROC [dbo].[usp_GetCube]
(@a INT, @b INT OUTPUT)
AS
BEGIN
  SET @b = (@a * @a * @a)
END
GO

Now to execute this procedure, first, we have to pass the value for the input parameter. And then, we have to pass a variable that can store the value of an output parameter. In the end, we will use a SELECT statement to display the value of the output parameter.

USE [sqlserverguides]
GO

DECLARE @cube INT

EXEC [dbo].[usp_GetCube] 9, @cube OUTPUT

SELECT @cube AS [Cube Value]

In the end, we will get the following result.

Read: Stored procedure for search functionality in SQL Server

How to execute stored procedure with multiple parameters

In SQL Server, a stored procedure can also have multiple input as well as output parameters. Now, these parameters can either be used to implement some task within the procedure, or they may be used to return some value from the procedure to the caller.

A procedure in SQL Server can have a maximum of 2100 parameters, each assign with a name, data type, direction, or a default value. Now to execute a stored procedure with multiple parameters, we can either specify the parameter name with its value or we can directly specify the parameter values in sequence.

Let’s understand this implementation, by executing a stored procedure having multiple parameters. And the code of an example is given below.

USE [sqlserverguides]
GO

CREATE PROC [dbo].[usp_FilterRecods]
( @country VARCHAR(MAX), @start_date DATE, @end_date DATE )
AS
BEGIN
  SELECT [first_name], [last_name], [country], [Date] AS [Joining Date]
  FROM [SampleTable]
  WHERE ([Country] = @country AND ([Date] BETWEEN @start_date AND @end_date))
END
GO

In the given example. we have created a stored procedure with 3 input parameters. And we will use these input parameters to filter out a resultset from a sample table. In the example, we are using the SELECT statement to fetch 4 columns from the sample table. After this, we are using a using WHERE clause to filter the result based upon input parameters.

Next, to execute this stored procedure, we will pass the values of input parameters directly in sequence.

USE [sqlserverguides]
GO

EXEC [dbo].[usp_FilterRecods] 'United States', '2020-03-13', '2021-09-13'

After successfully executing the above example, w will get the following output.

Read: Try catch in SQL Server stored procedure

Execute stored procedure with parameters in SQL server management studio

Now, we will understand how to use the SQL Server Management Studio for executing a stored procedure having parameters. For this implementation, we have to follow the following steps given below.

• First, start the SQL Server Management Studio and connect to the Database Engine using your credentials.
• Now, under Object Explorer, expand the database that contains the stored procedure.
• Next, expand the Programmability directory and then under Programmability, expand Stored Procedures.
• Now, right-click the required stored procedure and click on Execute Stored Procedure. It will open a Execute Procedure dialog box.
• Next, in the Execute Procedure dialog box, provide the required parameter values and click on OK to execute the procedure.

In the example, we are executing a stored procedure that filters out the result based upon 3 input parameters. And in the example, we have provided the values for each input parameter.

Now, if you are executing a stored procedure having an output parameter then, you don’t need to provide the value for it. The SQL Server Management Studio will automatically return the value of an output parameter into a variable.

Read: SQL Server stored procedure parameters

How to execute stored procedure without parameters in SQL Server

In SQL Server, we can also create a stored procedure without any parameters. A stored procedure without any parameters is one of the basic implementations of a stored procedure in SQL Server. These types of stored procedures are generally used when we just want to return resultsets.

Let’s understand this approach with the help of an example. For this example, we will create a simple stored procedure that returns a resultset. To return a resultset, we have to use the SELECT statement within the body of the procedure. The example of the stored procedure is illustrated below.

USE [sqlserverguides]
GO

CREATE PROCEDURE [dbo].[GetAllProducts]
AS
BEGIN
  SELECT [product_id], [product_name], [quantity], [product_price] 
  FROM dbo.ProductTable;
END
GO

In the above example, we have created a stored procedure that returns a resultset from the product table. Now to execute this procedure, we simply need to use the EXEC keyword and specify the name of the procedure.

The query for execution and its output are shown in the image below.

Read: SQL Server stored procedure return value

Execute stored procedure with date parameters in sql server

In SQL Server, a stored procedure can have parameters of different data types. A stored procedure in SQL Server supports different types of data types for the parameters. These data types include integer, float, varchar, date, DateTime, etc.

In this section, we will understand how we can execute a stored procedure with date data types parameters.

And we will discuss this implementation with the help of an example. Now, for example, we have created a simple stored procedure that converts a DateTime value to a date value. The complete code for the stored procedure is given below.

USE [sqlserverguides]
GO

Create proc [dbo].[usp_Datetime2Date]
( @datetime DATETIME, @date DATE OUT )
AS
BEGIN
	SET @date = CAST(@datetime AS DATE)
END
GO

• In the above example, we have created a stored procedure with 2 parameters.
• The first parameter is an input parameter of Datetime data type and the second parameter is an output parameter of Date data type.
• And this stored procedure converts the input parameter value of DateTime data type into the date data type.
• In the end, it returns the converted value using the output parameter.

Next, let’s execute this procedure by specifying the value for input and output parameters.

USE [sqlserverguides]
GO

DECLARE @date_value Date

EXEC [dbo].[usp_Datetime2Date] '2018-08-25 15:47:43.903', @date_value OUTPUT

SELECT @date_value AS [Date Value]

After successfully executing the given example, you will get the following output.

Read: SQL Server stored procedure vs function

Execute stored procedure with table-valued parameters in SQL Server

A table-valued parameter in SQL Server is a standard way to provide a table as a parameter into the stored procedures. It allows us to use a table as an input to a procedure, eliminating the need for more complex steps to complete the task.

So, in this section, we will discuss how to create and execute a stored procedure with table-valued parameters. For this implementation, we have to follow the following given sequence.

• First, create a table type that can be passed as a table-valued parameter
• Next, create a stored procedure that uses the table-valued parameter
• In the end, populate the table type and pass it to a stored procedure.

Let’s first understand how we can create a table-valued parameter in SQL Server. For this, we will have to use the CREATE TYPE command, here is the example of and table type that we have created.

USE [sqlserverguides]
GO

CREATE TYPE [dbo].[CustomerType] AS TABLE(
    [Id] [int] NULL,
    [Name] [varchar](50) NULL,
    [Country] [varchar](50) NULL
)
GO

Next, we have to create a stored procedure that accepts the CustomerType table type as a parameter. Here is an example of this stored procedure.

USE [sqlserverguides]
GO

CREATE PROCEDURE [dbo].[usp_InsertCustomers]
    @tblCustomers CustomerType READONLY
AS
BEGIN

    INSERT INTO Customers(Id, Name, Country)
    SELECT Id, Name, Country FROM @tblCustomers
END

In the above example, we have created a stored procedure with a table-valued parameter. And we will use the values of this table-valued parameter to insert some records in the customer table.

Next, we have to create a table variable of the CustomerType type and then we will add some records in the table variable. In the end, we will pass the table variable to the stored procedure as a table-valued parameter. The script for this implementation is given below.

USE [sqlserverguides]
GO

DECLARE @tblCustomers CustomerType
 
INSERT INTO @tblCustomers([Id], [Name], [Country])
VALUES(1, 'John Cena', 'United States'),
      (2, 'Peter Parker', 'France'),
      (3, 'Chris Woakes', 'England')
  
 
EXEC [dbo].[usp_InsertCustomers] @tblCustomers

Now, if we execute the above query, the given 3 records will be inserted into the customer’s table.

Read: SQL Server stored procedure insert into

How to execute stored procedure in SQL Server with return value

In SQL Server, whenever we execute a stored procedure, it by default returns an integer value to the user or the caller. These integer values indicate the execution status of a procedure. Generally, a zero represents successful execution and a non-zero represents some sort of error.

Other than this, we can also use the RETURN statement to return an integer value to the user or application. So, in this section, we will understand how we can execute a stored procedure with return value in SQL Server.

Let’s understand the implementation by executing a simple stored procedure that accepts an integer value as an input parameter. And returns the square of the integer using the RETURN statement. The query for the stored procedure is given below.

USE [sqlserverguides]
GO

CREATE PROC [dbo].[usp_GetSquare]
( @a INT )
AS
BEGIN
  RETURN (@a*@a)
END
GO

Now to execute this procedure and get the return value, first, we have to declare a variable of integer data type. Next, we have to use the variable in the execution statement and assign the procedure name with its parameter value to the variable. In the end, use the SELECT statement with the variable to get the return value.

USE [sqlserverguides]
GO

DECLARE @square_value INT

EXEC @square_value = [dbo].[usp_GetSquare] 9

SELECT @square_value AS [Return value]

Here is the output of the above execution.

Read: SQL Server stored procedure naming convention

How to execute stored procedure in select statement SQL Server

In SQL Server, we cannot directly use a SELECT statement to execute and fetch the result of a stored procedure. But still, there is one way through which we can execute a stored procedure within a SELECT statement in SQL Server. For this implementation, we have to use OPENQUERY to execute the procedure.

The OPENQUERY is a function in SQL Server that can be used in the SELECT statement. And this function is used to fetch data from one server to another. So, we will use this function to execute a procedure, and we will use the function in the SELECT statement to fetch the result from a stored procedure.

Let’s understand this implementation with the help of an example. For this demonstration, we are using a stored procedure that simply returns the records from the product table. The stored procedure is as follows.

USE [sqlserverguides]
GO


CREATE PROCEDURE [dbo].[GetAllProducts]
AS
BEGIN
  SELECT [product_id], [product_name], [quantity], [product_price] 
  FROM dbo.ProductTable;
END
GO

Now, we will use the following query to execute the procedure in the SELECT statement using the OPENQUERY function.

SELECT * FROM OPENQUERY([DESKTOP-0EMHCK4\SQLEXPRESS], 
                        'EXEC sqlserverguides.dbo.GetAllProducts');

In the OPENQUERY function, we first specified the server name, and after this, we specified the procedure execution statement. In the end, we will get the following output.

Read: SQL Server stored procedure if else

Execute stored procedure within stored procedure with parameters

In SQL Server, we can also use one stored procedure within the body of another stored procedure. And both the stored procedure can also have parameters to pass values between one another.

For a better understanding, let’s implement an example and try to understand the whole execution. So, first, we have created a stored procedure with the name “usp_GetProductById“. Now, this procedure accepts a product id as an input parameter and uses it to fetch the name and price of that product from the product table. In the end, it returns the product name and price using output parameters.

The script for the procedure is given below.

USE [sqlserverguides]
GO

CREATE PROC [dbo].[usp_GetProductById]
( @id INT, @name VARCHAR(MAX) OUT, @price INT OUT )
AS
BEGIN
  SELECT @name = [product_name], @price = [product_price] FROM dbo.ProductTable
  WHERE [product_id] = @id
END
GO

Next, we have created another stored procedure with the name “usp_GetProductPrice“. Now, this procedure also accepts the product id as an input parameter and it uses the parameter to execute the “usp_GetProductById” procedure. After execution, it will print the name and price of the product that is returned by the “usp_GetProductById” procedure.

The script for the procedure is given below.

USE [sqlserverguides]
GO

CREATE PROC [dbo].[usp_GetProductPrice]
( @id INT )
AS
BEGIN
  DECLARE @p_name VARCHAR(MAX), @p_price INT
  EXEC [dbo].[usp_GetProductById] @id, @p_name OUT, @p_price OUT
  PRINT 'The '+@p_name+' is having a price of ' + CAST(@p_price AS VARCHAR(MAX)) 
END
GO

Now, we will simply execute the “usp_GetProductPrice” procedure by providing the value for the input parameter.

USE [sqlserverguides]
GO

EXEC [dbo].[usp_GetProductPrice] 101

And after successfully executing the above example, we will get the following output.

Read: How to view stored procedure in SQL Server

Execute stored procedure with select statement as input parameters

In SQL Server, we generally use a SELECT statement to fetch data from different tables or functions. The data returned from the SELECT statement is known as a resultset. So, while working with stored procedures, we might come across a situation where we have to pass multiple values into the procedure.

But in SQL Server, we cannot pass a SELECT statement directly as an input parameter. The correct approach is to use a table-valued parameter to pass the table data into the procedure.

So, let’s understand the approach with the help of an example. And for this, again we are using the same usp_InsertCustomers procedure.

USE [sqlserverguides]
GO


CREATE PROCEDURE [dbo].[usp_InsertCustomers]
    @tblCustomers CustomerType READONLY
AS
BEGIN
    INSERT INTO Customers(Id, Name, Country)
    SELECT Id, Name, Country FROM @tblCustomers
END
GO

Now, for execution, first, we will declare a table-type variable. After this, we can use a SELECT statement to insert the values into the table-type variable. And in the end, we will pass the table-type variable as an input parameter.

USE [sqlserverguides]
GO

DECLARE @tblCust CustomerType

INSERT INTO @tblCust
SELECT Id, Name, Country FROM [CustomersTable2]

EXEC [dbo].[usp_InsertCustomers] @tblCust

After execution, 5 rows from CustomersTable2 will be inserted into the Customers table. Here is the output of the above execution.

Read: SQL Server select from stored procedure

How to pass dynamic parameters to stored procedure in sql server

In SQL Server, we generally create a stored procedure with some input parameters. And these input parameters are used to pass values into the procedure dynamically.

For demonstration consider the following stored procedure.

USE [sqlserverguides]
GO

CREATE PROC [dbo].[usp_FilterRecods]
( 
  @country VARCHAR(MAX) = 'United States', 
  @start_date DATE = NULL, 
  @end_date DATE = NULL 
)
AS
BEGIN
  SELECT [first_name], [last_name], [country], [Date] AS [Joining Date]
  FROM [SampleTable]
  WHERE ([Country] = @country AND ([Date] BETWEEN @start_date AND @end_date))
END
GO

In the above example, we have created a stored procedure with 3 input parameters. And we will use these parameters to filter out records from a sample table. Moreover, we have also defined a default value for each parameter. So, even if we don’t pass any value for the input parameter, the procedure will use the default values for the operation.

Here is an execution example of this procedure.

USE [sqlserverguides]
GO

EXEC [dbo].[usp_FilterRecods] @start_date = '2020-03-13', @end_date = '2021-09-13'

In the execution, we have not defined the value country name parameter. So, the procedure will use the default value for the country and return the following output.

You may also like: Advanced Stored Procedure Examples in SQL Server

In this SQL Server tutorial, we have learned How to execute stored procedures in SQL Server. We have also discussed different examples related to the execution of stored procedures in SQL Server. Here is the complete list of topics that we have covered.

• How to execute stored procedure in SQL Serer
• How to execute stored procedures automatically in SQL Server
• Execute stored procedure with parameters in SQL Server Management Studio
• How to execute stored procedure without parameters in SQL Server
• How to execute stored procedure with parameters in SQL Server
• Execute stored procedure with multiple parameters
• How to execute stored procedure in sql server with input parameters
• How to execute stored procedure in sql server with output parameters
• Execute stored procedure with input and output parameters in SQL Server
• How to execute stored procedure in select statement SQL Server
• Execute stored procedure within stored procedure with parameters
• How to pass dynamic parameters to stored procedure in SQL Server
• Execute stored procedure with select statement as input parameters
• Execute stored procedure with date parameters in SQL Server
• Execute stored procedure with table-valued parameters in SQL Server
• How to execute stored procedure in SQL Server with return value