August 25, 2017 at 1:45 pm
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
August 25, 2017 at 2:13 pm
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
August 26, 2017 at 3:29 am
Drew, thank you!
Julian
August 28, 2017 at 4:19 am
Final solution here: https://www.sqlservercentral.com/Forums/FindPost1894101.aspx
August 28, 2017 at 5:15 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply