Filtering query by date built from integers

Question

I am trying to make a stored procedure and pass some parameters for filtering. As soon as filtering is in horizon, code has to be as generic as possible with as few as possible if, else, etc.

How could I make this more readable? Can I use functions for this?

ALTER PROCEDURE [dbo].[Stats] @network VARCHAR(9) = NULL, 
                                               @name    VARCHAR(100) = NULL, 
                                               @version VARCHAR(15) = NULL, 
                                               @year    INT = NULL, 
                                               @month   INT = NULL,
                                               @day INT = NULL,
                                               @from DATE = null,
                                               @to DATE = null
AS 
  BEGIN 
      SET nocount ON;

          DECLARE @fromYear INT = NULL;
      SET @fromYear = CASE WHEN @from IS NOT NULL THEN Datepart(year, @from) END

          DECLARE @fromMonth INT = NULL;
      SET @fromMonth = CASE WHEN @from IS NOT NULL THEN Datepart(month, @from) END

          DECLARE @fromDay INT = NULL;
      SET @fromDay = CASE WHEN @from IS NOT NULL THEN Datepart(day, @from) END

          DECLARE @toYear INT = NULL;
      SET @toYear = CASE WHEN @to IS NOT NULL THEN Datepart(year, @to) ELSE @fromYear END

          DECLARE @toMonth INT = NULL;
      SET @toMonth = CASE WHEN @to IS NOT NULL THEN Datepart(month, @to) ELSE @fromMonth END

          DECLARE @toDay INT = NULL;
      SET @toDay = CASE WHEN @to IS NOT NULL THEN Datepart(day, @to) ELSE @fromDay END


      SELECT GS.[contractaddress], 
             GS.[network], 
             GS.[rounds], 
             GS.[sessions], 
             GS.[handle], 
             GS.[hold], 
             GS.[year], 
             GS.[month], 
             GS.[day], 
             G.[name], 
             G.[version],
             DATEFROMPARTS(GS.[Year], GS.[Month], GS.[Day]) as [Date]
      FROM   [dbo].[gamestatsdaily] AS GS 
             INNER JOIN [dbo].[game] AS G 
                     ON GS.contractaddress = G.[contractaddress] 
      WHERE  ( @network IS NULL 
                OR ( GS.network = Upper(@network) ) ) 
             AND ( @name IS NULL 
                    OR ( GS.[contractaddress] IN (SELECT [contractaddress] 
                                                  FROM   [dbo].[game] 
                                                  WHERE  [name] = @name 
                                                         AND [version] = 
                                                             @version 
                                                         AND [network] = 
                                                             @network) 
                       ) ) 
             AND ( @year IS NULL OR GS.[Year] = @year )
             AND ( @month IS NULL OR GS.[Month] = @month )
             AND ( @day IS NULL OR GS.[Day] = @day )
             AND ( @from IS NULL OR ( GS.[Year] <= @fromYear AND @toYear >= GS.[Year] AND GS.[Month] <= @fromMonth AND @toMonth >= GS.[Month] AND GS.[Day] <= @fromMonth AND @toMonth >= GS.[Day]))

      ORDER  BY GS.[year] ASC, 
                GS.[month] ASC, 
                GS.[day] ASC 
  END

aduguid · Accepted Answer · 2019-01-03 10:22:29Z

Here is the revised SQL I came up with

Below this code is a list of tips for writing better SQL.

ALTER PROCEDURE [dbo].[Stats] 
      @network VARCHAR(9) = NULL
    , @name VARCHAR(100) = NULL
    , @version VARCHAR(15) = NULL
    , @year INT = NULL
    , @month INT = NULL
    , @day INT = NULL
    , @from DATE = NULL
    , @to DATE = NULL
AS

BEGIN

    BEGIN TRY

        SET NOCOUNT ON;

        WITH
        contract_address_list
        AS
        (
            SELECT DISTINCT
                [contractaddress]
            FROM 
                [dbo].[game]
            WHERE 
                1=1
                AND [name] = @name
                AND [version] = @version
                AND [network] = @network
        )
        SELECT 
              GS.[contractaddress]
            , GS.[network]
            , GS.[rounds]
            , GS.[sessions]
            , GS.[handle]
            , GS.[hold]
            , GS.[year]
            , GS.[month]
            , GS.[day]
            , G.[name]
            , G.[version]
            , [Date] = DATEFROMPARTS(GS.[Year], GS.[Month], GS.[Day])
        FROM 
            [dbo].[gamestatsdaily] AS GS
            INNER JOIN [dbo].[game] AS G ON GS.[contractaddress] = G.[contractaddress]
            LEFT JOIN contract_address_list AS CAL ON CAL.[contractaddress] = GS.[contractaddress]
        WHERE 
            1=1
            AND (@name IS NULL OR CAL.[name] IS NOT NULL)
            AND (@network IS NULL OR (GS.network = UPPER(@network)) )
            AND (@year IS NULL OR GS.[Year] = @year )
            AND (@month IS NULL OR GS.[Month] = @month )
            AND (@day IS NULL OR GS.[Day] = @day )
            AND (
                @from IS NULL
                OR (
                        GS.[Year] >= DATEPART(year, @from)
                    AND GS.[Year] <= COALESCE(DATEPART(year, @to), DATEPART(year, @from))
                    AND GS.[Month] >= DATEPART(month, @from)
                    AND GS.[Month] <= COALESCE(DATEPART(month, @to), DATEPART(month, @from))
                    AND GS.[Day] >= DATEPART(day, @from)
                    AND GS.[Day] <= DATEPART(day, @to)
                    )
                )
        ORDER BY 
              GS.[year] ASC
            , GS.[month] ASC
            , GS.[day] ASC

    END TRY
    BEGIN CATCH
        SELECT 
                [ErrorNumber] = ERROR_NUMBER()
            , [ErrorSeverity] = ERROR_SEVERITY()
            , [ErrorState] = ERROR_STATE()
            , [ErrorProcedure] = ERROR_PROCEDURE()
            , [ErrorLine] = ERROR_LINE()
            , [ErrorMessage] = ERROR_MESSAGE();
    END CATCH

END

Tips for your SQL

For formatting, I would download the following tool, Poor Man's T-Sql Formatter and on GitHub. I use it when I have to edit other developer's code.

Here are the settings I used

I would put the commas in front to clearly define new columns. Versus code wrapped in multiple lines. It also makes trouble-shooting code easier.
If you put 1=1 at the top of a WHERE condition, it enables you to freely change the rest of the conditions when debugging a query. The SQL query engine will end up ignoring the 1=1 so it should have no performance impact. Reference
If you use common table expressions (CTE) in your SQL it helps with documentation. The expression name can then let other developers know why you used that expression e.g. contract_address_list or active_projects.
Use the COALESCE function to evaluate the expression in the WHERE clause for the DATEPARTS.
I find it more convenient to see column alias's referenced from the left side e.g. , [Date] = DATEFROMPARTS(GS.[Year], GS.[Month], GS.[Day])
Avoid using keywords as object names. Microsoft Reference
Check the Estimated Execution Plan Ctrl + L in Microsoft SQL Server Management Studio (SSMS) to check the performance of the query by pasting both SQL statements in a query window.

Here's an example.

Stack Exchange Network

Filtering query by date built from integers

1 Answer 1

Here is the revised SQL I came up with

Tips for your SQL

You must log in to answer this question.

Hot Network Questions

Filtering query by date built from integers

1 Answer 1

Here is the revised SQL I came up with

Tips for your SQL

You must log in to answer this question.

Related

Hot Network Questions