I'm trying to write a table-valued function, in SQL Server 2012, that makes a call to another table-valued function based on an input parameter, here's an example
CREATE FUNCTION [dbo].[fnMyLastFilterFunction]
(
@Param1 VARCHAR(64),
@Param2 VARCHAR(64),
@Param3 INT,
@StartDate DATETIME,
@EndDate DATETIME
)
RETURNS TABLE
AS
RETURN
(
IF (@Param3 = 0)
BEGIN
SELECT t1.Col1, t1.Col2, t1.Col3, t1.Col4
FROM fnMyFirstFilterFunction(@Param1,@Param2) t1
WHERE t1.DateTimeEnd BETWEEN @StartDate AND @EndDate
END
)
When I execute this I get two errors:
Is it possible to have such a condition in a table-valued function? Basically I'm trying to implement a filter system starting with all data in "fnMyFirstFilterFunction". So "fnMyLastFilterFunction" takes that data and filters it by @start and @end date. I'm using stored functions to separate the filter stages. My SQL is rusty, so if there's a better way of doing this, I'm open to ideas.
UPDATE:
SpectralGhost's suggestion compiled fine, but what if in the function body I want the logic to call different functions based on @Param3's value? Such as the following:
IF (@Param3 = 0)
BEGIN
SELECT t1.Col1, t1.Col2, t1.Col3, t1.Col4
FROM fnMyFirstFilterFunction(@Param1,@Param2) t1
WHERE t1.DateTimeEnd BETWEEN @StartDate AND @EndDate
END
ELSE IF (@Param3 = 1)
BEGIN
SELECT t1.Col1, t1.Col2, t1.Col3, t1.Col4
FROM fnOtherFilterFunction(@Param1,@Param2) t1
WHERE t1.DateTimeEnd BETWEEN @StartDate AND @EndDate
END
Get rid of the IF and replace with this query.
SELECT t1.Col1, t1.Col2, t1.Col3, t1.Col4
FROM fnMyFirstFilterFunction(@Param1,@Param2) t1
WHERE t1.DateTimeEnd BETWEEN @StartDate AND @EndDate
AND @Param3 = 0
For the edit, try
SELECT t1.Col1, t1.Col2, t1.Col3, t1.Col4
FROM fnMyFirstFilterFunction(@Param1,@Param2) t1
WHERE t1.DateTimeEnd BETWEEN @StartDate AND @EndDate
AND @Param3 = 0
UNION ALL
SELECT t1.Col1, t1.Col2, t1.Col3, t1.Col4
FROM fnOtherFilterFunction(@Param1,@Param2) t1
WHERE t1.DateTimeEnd BETWEEN @StartDate AND @EndDate
AND @Param3 = 1
Since @Param3 will never be both 0 and 1 simultaneously this is logically equivalent to an if statement.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With