Nested sub queries with SUM, optional parameters in Storedprocedure

  • Hi,
    The following storedprocedure (seems) to work fine when I supply all the optional parameters.
    But if I leave out (ie don't supply a value) the parameter @IDAfd for example, no records are returned.
    Any suggestions?

    TIA and  Cheers,
    Julian


    IF EXISTS (SELECT * FROM sys.procedures    WHERE schema_id = schema_id('dbo') AND name = N'spHrsM' )
    DROP PROCEDURE dbo.spHrsM
    GO

    USE xxx;
    GO

    CREATE PROCEDURE    spHrsM 
                         @IDHUIS VARCHAR(4) = NULL
                        ,@IDAFD VARCHAR(4) = NULL
                        ,@JAAR INT = NULL
                        ,@WEEKBEGIN INT = NULL
                        ,@WEEKEND INT = NULL
    AS
    BEGIN
    SET NOCOUNT ON;

    SELECT IsoYear, IsoWknr AS IsoWeek, SUM(ISNULL(HrsM,0)) AS [UrenM]

    FROM    (

            SELECT    T.IsoWkNr, T.IsoYear,

                    (
                        SELECT        SUM(ISNULL(M.URENM,0))
                        FROM     Meerzorg M JOIN HuisAfdeling H ON
                                        M.IDHuis = H.IDHuis AND
                                        M.IDAfd = H.IDAfd
                                        
                        WHERE        M.DatumData <= T.[DATE]
                                    AND ((M.IDHuis = @IDHUIS) or (@IDHUIS = NULL))
                                    AND ((H.IDAfd = @IDAFD) or (@IDAFD = NULL))
                                    AND H.MeeTellen <> 'NEE'
                                    AND M.Active = 1
                                    
                    )    AS HrsM

            FROM    [dimTime] AS T
            WHERE    T.IsoYear = @JAAR
                    AND (T.IsoWkNr BETWEEN @WEEKBEGIN AND @WEEKEND)
                    
            ) x

    GROUP BY IsoYear, IsoWkNr
    ORDER BY IsoYear, IsoWkNr

    END

  • Yes, the problem is here.

    AND ((M.IDHuis = @IDHUIS) or (@IDHUIS = NULL))
               AND ((H.IDAfd = @IDAFD) or (@IDAFD = NULL))

    NULL never equals anything (even another NULL), unless you have changed the ANSI_NULLS setting.  The correct test is @IDHUIS IS NULL.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Drew, thank you!
    Julian

  • Final solution here: https://www.sqlservercentral.com/Forums/FindPost1894101.aspx

  • Please note that the code pattern used here has a severe performance problem.
    See https://www.simple-talk.com/content/article.aspx?article=2280 for explanations and options to fix

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply