SQL Stored Procedure Optional Parameter-CodePudding

I am trying to create a Stored Procedure as follows -

CREATE PROCEDURE [dbo].[Chargeable_Time] @DateFrom  DATE,
                                         @DateTo    DATE,
                                         @Allocated INT = NULL
AS
    SELECT t.CaseID,
           c.DisplayNo,
           c.CaseName,
           c.[Category],
           t.[Date],
           t.FeeEarner,
           u.Username,
           t.ChargeCode,
           t.[Hours],
           t.Fees,
           t.GroupID,
           ( CASE
               WHEN t.GroupID IS NOT NULL THEN 1
               WHEN t.GroupID IS NULL THEN 0
             END ) AS Allocated
    FROM   Timesheet AS t
           LEFT JOIN [User] AS u
                  ON t.FeeEarner = u.ID
           LEFT JOIN [Case] AS c
                  ON t.CaseID = c.ID
    WHERE  t.Active = 'True'
           AND t.CaseID IS NOT NULL
           AND t.Rate > 0
           AND t.[Date] >= @DateFrom
           AND t.[Date] <= @DateTo
           AND Allocated = @Allocated

However I have two problems -

The Allocated field is not recognised as it is created by the procedure rather than being a field in the underlying tables, and

I want to be able to return all data if the Allocated field is left NULL. As it currently stands anyone calling the procedure can enter a 0 or 1 to filter the results, but I want them to also be able to leave the parameter blank and get the full set without the filter. I am not sure how to do this.

Any help would be greatly appreciated.

CodePudding user response：

You can change the last part of the code as follows

Create PROCEDURE [dbo].[Chargeable_Time] 
       @DateFrom  DATE,
       @DateTo    DATE,
       @Allocated INT = NULL
AS
Begin
   SELECT t.CaseID,
       c.DisplayNo,
       c.CaseName,
       c.[Category],
       t.[Date],
       t.FeeEarner,
       u.Username,
       t.ChargeCode,
       t.[Hours],
       t.Fees,
       t.GroupID,
       ( CASE
           WHEN t.GroupID IS NOT NULL THEN 1
           WHEN t.GroupID IS NULL THEN 0
         END ) AS Allocated
FROM   Timesheet AS t
       LEFT JOIN [User] AS u
              ON t.FeeEarner = u.ID
       LEFT JOIN [Case] AS c
              ON t.CaseID = c.ID
WHERE  t.Active = 'True'
       AND t.CaseID IS NOT NULL
       AND t.Rate > 0
       AND t.[Date] >= @DateFrom
       AND t.[Date] <= @DateTo
      AND (( CASE
           WHEN t.GroupID IS NOT NULL THEN 1
           WHEN t.GroupID IS NULL THEN 0
         END ) = @Allocated OR @Allocated is null  )
END
GO

CodePudding user response：

This should do the trick. However this can be a bad solution when there is lot's of data. You should test that. Some reading about this kind of solution: https://www.sqlskills.com/blogs/kimberly/high-performance-procedures/

CREATE PROCEDURE [dbo].[Chargeable_Time]
   @DateFrom DATE,
   @DateTo DATE,
   @Allocated INT = NULL
AS
   SELECT
         t.CaseID,
         c.DisplayNo,
         c.CaseName,
         c.[Category],
         t.[Date],
         t.FeeEarner,
         u.Username,
         t.ChargeCode,
         t.[Hours],
         t.Fees,
         t.GroupID,
         (CASE WHEN t.GroupID IS NOT NULL THEN 1
               WHEN t.GroupID IS NULL THEN 0
          END
         ) AS Allocated
   FROM  Timesheet AS t
         LEFT JOIN [User] AS u ON t.FeeEarner = u.ID
         LEFT JOIN [Case] AS c ON t.CaseID = c.ID
   WHERE t.Active = 'True'
         AND t.CaseID IS NOT NULL
         AND t.Rate > 0
         AND t.[Date] >= @DateFrom
         AND t.[Date] <= @DateTo
         AND (CASE WHEN t.GroupID IS NOT NULL THEN 1
                   WHEN t.GroupID IS NULL THEN 0
              END
             ) = @Allocated
         OR @Allocated IS NULL;