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
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 );
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.
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.
Optional parameters are widely used in programming as well as in stored procedures in T-SQL.
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))
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With