Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

TSQL Stored Functions

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:

  1. Incorrect syntax near the keyword 'IF'
  2. Incorrect syntax near ')' (i.e. the last closing parenthesis)

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
like image 428
kingrichard2005 Avatar asked Mar 19 '26 21:03

kingrichard2005


2 Answers

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
like image 95
UnhandledExcepSean Avatar answered Mar 22 '26 15:03

UnhandledExcepSean


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.

like image 21
Brian White Avatar answered Mar 22 '26 14:03

Brian White



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!