Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Stored Procedure with Optional DateTime Parameters in SQL Server

Tags:

sql-server

How to write this select query in SQL Server when the datetime parameters (@StartDate & @EndDate) are optional in this stored procedure?

CREATE PROCEDURE [dbo].[prSearchEmployees]
(
    @Id INT = NULL
    ,@FullName VARCHAR(20) = NULL
    ,@Age INT = NULL
    ,@StartDate = NULL
    ,@EndDate = NULL
)
AS
BEGIN
   SELECT * 
   FROM Employee 
   WHERE Id = ISNULL(@Id, Id) 
     AND FullName LIKE ISNULL(@FullName + '%', FullName)
     AND Age = ISNULL(@Age, Age)
END
like image 689
user1030181 Avatar asked Nov 10 '13 14:11

user1030181


People also ask

How do I pass a datetime parameter to a stored procedure in SQL Server?

CREATE PROCEDURE GetData ( @ID INT = NULL , @StartDate DATETIME = NULL , @EndDate DATETIME = NULL ) AS SET NOCOUNT ON; SELECT * FROM TableA A JOIN TableB B ON A. a = B. b WHERE ( @ID IS NULL OR S.ID = @ID ) AND ( @StartDate IS NULL AND @EndDate IS NULL OR DateColumn BETWEEN @StartDate AND @EndDate );

Can a stored procedure have optional parameters?

If you are executing a stored procedure with a bunch of parameters it can be a bit of a pain if you have to pass a value in for each of them. Fortunately, it's pretty easy to make some parameters required and others optional. You simply give them a default value.

What is optional parameter in stored procedure?

A parameter is considered optional if the parameter has a default value specified when it is declared. It is not necessary to provide a value for an optional parameter in a procedure call. The default value of a parameter is used when: No value for the parameter is specified in the procedure call.

Can SQL functions have optional parameters?

Optional parameters are widely used in programming as well as in stored procedures in T-SQL.


2 Answers

I know this is an old post but there are alternatives that are a bit more concise. This post outlines it perfectly. So in the previous example you would do something like

 CREATE PROCEDURE [dbo].[prSearchEmployees]
(
 @Id INT = NULL
,@FullName VARCHAR(20) = NULL
,@Age INT = NULL
,@StartDate DATETIME = NULL
,@EndDate DATETIME = NULL
)
AS
BEGIN    

SELECT * FROM Employee 
     WHERE ((@Id IS NULL) OR (Id = @Id))
       AND ((@FullName IS NULL) OR (FullName LIKE @FullName + '%'))
       AND ((@Age IS NULL) OR (Age = @Age))
       AND ((@SDate IS NULL) OR (Date BETWEEN @SDate AND @EDate))
like image 83
ChrisM Avatar answered Nov 08 '22 19:11

ChrisM


You could add two new variables at the top of your sproc.. And assign based on the ISNULL function

CREATE PROCEDURE [dbo].[prSearchEmployees]
(
 @Id INT = NULL
,@FullName VARCHAR(20) = NULL
,@Age INT = NULL
,@StartDate DATETIME = NULL
,@EndDate DATETIME = NULL
)
AS
BEGIN

DECLARE @SDate DATETIME
DECLARE @EDate DATETIME

SET @SDate = ISNULL(@StartDate, GETDATE())
SET @EDate = ISNULL(@EndDate, GETDATE())


SELECT * FROM Employee 
     WHERE Id = ISNULL(@Id, Id) 
       AND FullName LIKE ISNULL(@FullName + '%', FullName)
       AND Age = ISNULL(@Age, Age)
       AND Date BETWEEN @SDate AND @EDate
like image 12
Sam Avatar answered Nov 08 '22 20:11

Sam