In this SQL Server tutorial, I will show you how to execute a stored procedure in SQL server with a date parameter.
You will be able to understand how to create a stored procedure that accepts the date values and returns the information from the table based on the given date ranges.
Execute Stored Procedure in SQL Server with Date Parameter
When you work with time-sensitive data, suppose you have created a stored procedure that returns information based on the provided date and time. Sometimes, when you execute that kind of stored procedure, you get an error.
To resolve that kind of issue, here you will understand how to create a stored procedure that accepts the date parameter and executes it correctly so that it should raise any error.
If you don’t know how to create a stored procedure, then refer to the tutorial SQL Server Stored Procedure Tutorials.
But first, you need to create a stored procedure, so here I will use the CustomerOrders table, which contains information about OrderID, CustomerName, and OrderDate. The table is shown below.
Execute Stored Procedure in SQL Server with Date Parameter using query
Now, you have a task to create a stored procedure to return the customer order based on the provided date range.
Consider that If you want to know the Order between two dates 2023-11-01 and 2023-11-10, then the procedure must return all the Orders in that date range.
So, create a procedure named GetOrdersByDate using the below query.
CREATE PROCEDURE GetOrdersByDate
@StartDate DATE,
@EndDate DATE
AS
BEGIN
SELECT OrderId, OrderDate, CustomerName FROM CustomerOrders
WHERE OrderDate >= @StartDate AND OrderDate <= @EndDate;
END;
When you execute the above query, a procedure named GetOrdersByDate is created in your database.
- Within GetOrdersByDate, two variable @StartDate and @EndDate of type Date, is declared.
- After that, within the BEGIN and END blocks, the statement returns the customer orders based on the provided date value as a parameter while executing the procedure.
Now you want to know the customer’s name who ordered the items between ‘2023-11-20’ and ‘2023-12-15’. For that, execute the GetOrdersByDate procedure with the given date parameter using the below query.
EXEC GetOrdersByDate '2023-11-20', '2023-12-15';
After executing the above query, the procedure GetOrdersByDate returns all the customer names who made the order between 2023-11-20 and 2023-12-15, as you can see in the above output.
Now understand the query EXEC GetOrdersByDate ‘2023-11-20’, ‘2023-12-15’, here you are calling the procedure GetOrdersByDate using the EXEC statement with date parameter.
- While defining the GetOrdersByDate store procedure, you have specified the two-parameter of type date @StartDate and @EndDate that this procedure would accept date value.
- When calling the procedure, you have provided those two date parameters. This is how you can execute stored the procedure in SQL Server with the date parameter.
- So, always specify the date parameter after the procedure name and separate each parameter by a comma.
- In other words, the number of parameters you define in the stored procedure and the same number of parameter values should be provided while executing the store procedure, and a comma should separate each parameter.
But here, you need to notice one thing: the GetOrdersByDate procedure returns a result set, so the question is how to retrieve a single value as output from the stored procedure.
The GetOrdersByDate is based on without OUTPUT parameter. Let’s take an example where you will learn how to use the OUTPUT parameter with stored procedure.
Execute Stored Procedure in SQL Server with Date Output Parameter using query
Suppose you have to design a stored procedure that should compute the total number of orders within the given date range, and the procedure must return the result as an OUTPUT parameter.
So, use the below query to create a procedure named GetTotalOrders.
CREATE PROCEDURE GetTotalOrders
@StartDate DATE,
@EndDate DATE,
@TotalOrders INT OUTPUT
AS
BEGIN
SELECT @TotalOrders = Count(OrderID)
FROM CustomerOrders
WHERE OrderDate >= @StartDate AND OrderDate <= @EndDate;
END;
After the execution of the above query, a new GetTotalOrders procedure is created. In that procedure, the new @TotalOrders variable of type INT is declared with the OUTPUT parameter.
When you declare any variable with an OUTPUT parameter, it means that the variable will return data from the stored procedure to the caller. In this case, the variable returns the total number of orders.
Use the GetTotalOrders procedure and compute the total number of orders between the dates ‘2023-11-15’ and ‘2023-12-01’.
DECLARE @DateResult INT;
EXEC GetTotalOrders '2023-11-15', '2023-12-01', @DateResult OUTPUT;
SELECT @DateResult;
When you execute the GetTotalOrders procedure, it returns the value 17, which is the total number of orders.
As you know, the procedure returns the computed single value through the @TotalOrders variable, which is the OUTPUT parameter.
To receive that returned value, a new variable @DateResult of type INT is declared, and then the returned value is stored in that variable. Remember you need to explicitly specify that the @DateResult is the OUTPUT parameter while executing the procedure.
To access the store value within @DateResult, simply use the SELECT @DateResult statement. So here, you provide the date range and get the single value by executing the store procedure with the date parameter.
This is how to execute stored procedure in SQL Server with date output parameter.
Execute Stored Procedure in SQL Server with Date Parameter using SSMS
You can also execute the stored procedure through SQL Server Management Studio, so here, you will learn how to execute the stored procedure that you have created in the above section using SSMS.
Open the SQL Server Management Studio and connect to the SQL Server instance.
Then, expand the Databases node from the Object Explorer panel. After that, within Databases, the node expands the E_Commerce (in your case, it can be any database), then expands the Programmability subnode, and then expands the Stored Procedures subfolder, as shown below.
- Databases>E_Commerce>Programmability>Stored Procedures
Under the System Procedures folder, you can see the dbo.GetOrdersByDate procedure. To execute this procedure, right-click on it and choose the option Execute Stored Procedure, as shown in the below picture.
After clicking on Execute Stored Procedure, the Execute Procedure dialog appears where you need to provide a date range value for the parameters @StartDate and @EndDate with the value column, as shown in the screenshot below.
In the value column, two values are provided, ‘2023-11-10’ and ‘2023-12-01’ for @StartDate and @EndDate, respectively.
Then click on the OK button to execute the GetOrdersByDate procedure, as shown in the above picture.
When you click on the OK button, it automatically creates a query with the provided date range and executes that query that you can see in the above output.
After execution of the procedure, the result is shown, which contains the customer name who made the order between ‘2023-11-10’ and ‘2023-12-01’. This is how to execute a stored procedure in SQL Server with date parameter using SSMS
Conclusion
In this SQL Server tutorial, you learned how to execute SQL Server stored procedure with date parameters. You create two stored procedure: one accepts the date parameter and returns the result set, and the other accepts the date parameter and return the OUTPUT.
You may also like:
- How to Give Permission to Execute Stored Procedure in SQL Server?
- How to Execute Stored Procedure for Update in SQL Server?
- How to Execute Stored Procedure for Insert in SQL Server?
After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.