Nested sub queries with SUM, optional parameters in Storedprocedure

• JJR333

  Default port

  Points: 1469

  More actions

  August 25, 2017 at 1:45 pm

  #332311

  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

• drew.allen

  SSC Guru

  Points: 77006

  More actions

  August 25, 2017 at 2:13 pm

  #1956948

  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

• JJR333

  Default port

  Points: 1469

  More actions

  August 26, 2017 at 3:29 am

  #1956969

  Drew, thank you!
  Julian

• JJR333

  Default port

  Points: 1469

  More actions

  August 28, 2017 at 4:19 am

  #1957051

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

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  August 28, 2017 at 5:15 am

  #1957054

  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