December 1, 2014 at 4:13 am
Hi,
I have troubles with this scalar-valued UDF:
I get the error:
Select statements included within a function cannot return data to a client.
Is this a proper way to include a CTE in a function?
Thanks!
USE [DB1]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[_Pink_FN_StartingDatePLGeographical](@StartingDate AS DATETIME) RETURNS NVARCHAR(20)
AS
BEGIN
DECLARE @Enddate DATETIME
SET @Enddate = '2014-10-26'
DECLARE @PastPeriods INT
SELECT @PastPeriods =/* Determine number of periods to go back from enddate-period to get startdate for SSRS report */
CASE
WHEN MONTH(@Enddate) IN (1, 4, 7, 10) THEN 15
WHEN MONTH(@Enddate) IN (2, 5, 8, 11) THEN 16
WHEN MONTH(@Enddate) IN (3, 6, 9, 12) THEN 14
ELSE 0
END;
WITH Startdate AS
(
SELECTROW_NUMBER() OVER (ORDER BY StartDate) AS RowCounter,
EndDate,
StartDate,
FreeIntField_01 AS Year,
FreeIntField_02 AS Period
FROM Absences
WHEREType = 97 AND Status NOT IN (2, 5)
)
SELECTs2.StartDate AS StartDate
FROMStartdate s1
LEFT OUTER JOINStartdate s2 ON s1.RowCounter - @PastPeriods = s2.RowCounter
WHEREs1.EndDate = @Enddate
RETURN (@Startingdate);
END
GO
December 1, 2014 at 4:22 am
I think you just need to add [font="Courier New"]@Startingdate =[/font] before [font="Courier New"]s2.StartDate[/font]. Can you guarantee that the SELECT query will always return a single row only?
John
December 1, 2014 at 4:45 am
Yes it is. Why not code it up as an inline table-valued function instead of a UDF? You're likely to get far better performance. Get the query to work and you're almost there.
I'm not convinced you need to join the CTE to itself for this, you should be able to apply the filter to the CTE but without sample data it's difficult to tell:
WITH OrderedAbsences AS (
SELECT
RowCounter = ROW_NUMBER() OVER (ORDER BY StartDate),
EndDate,
StartDate,
FreeIntField_01 AS [Year],
FreeIntField_02 AS Period
FROM Absences
WHERE [Type] = 97
AND [Status] NOT IN (2, 5)
)
SELECT
s2.StartDate AS StartDate
FROM OrderedAbsences s1
CROSS APPLY ( -- Determine number of periods to go back from enddate-period to get startdate for SSRS report
SELECT PastPeriods =
CASE
WHEN MONTH(CAST('2014-10-26' AS DATETIME)) IN (1, 4, 7, 10) THEN 15
WHEN MONTH(CAST('2014-10-26' AS DATETIME)) IN (2, 5, 8, 11) THEN 16
WHEN MONTH(CAST('2014-10-26' AS DATETIME)) IN (3, 6, 9, 12) THEN 14
ELSE 0
END
) x
LEFT OUTER JOINOrderedAbsences s2
ON s1.RowCounter - x.PastPeriods = s2.RowCounter
WHERE s1.EndDate = CAST('2014-10-26' AS DATETIME) --@Enddate
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 1, 2014 at 2:54 pm
That is a way better approach! Thanks!
December 1, 2014 at 4:21 pm
You can pass @StartingDate as a param to the ITV function.
Also, you can simplify the calc for PastPeriods:
MONTH(@StartingDate) % 3 + 14
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply