Return to Revisions

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

1. 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

2. 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.

3. 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

4. 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.

5. Use the COALESCE function to evaluate the expression in the WHERE clause for the DATEPARTS.

6. 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])

7. Avoid using keywords as object names. Microsoft Reference

8. 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.