Inline Table Functions with IF or Case Statement

  • You can use derived tables within the query to provide computed values, you don't need variables.

    Also, here's a quick demo to show that you can query from different tables, as long as the results sets are compatible:

    CREATE FUNCTION dbo.test_diff_queries_in_a_function

    (

    @Usage_Date SmallDAteTime,

    @RateCode Varchar(4)

    )

    RETURNS TABLE AS

    RETURN

    SELECT t.object_id

    FROM sys.tables t

    WHERE @RateCode = 'T'

    UNIO N AL L --Edit: I can't code this correctly or my work filter rejects it as "sql injection"

    SELECT TOP (100) column_id

    FROM sys.columns c

    INNER JOIN sys.tables t ON t.object_id = c.object_id

    WHERE c.name LIKE '%id%'

    AND @RateCode = 'C'

    GO

    SELECT * FROM dbo.test_diff_queries_in_a_function (GETDATE(), 'T')

    SELECT * FROM dbo.test_diff_queries_in_a_function (GETDATE(), 'C')

    GO

    DROP FUNCTION dbo.test_diff_queries_in_a_function

    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".

  • Or

    create function dbo.SomeFunction(

    @parm1 int,

    @parm2 varchar(4)

    )

    returns table as

    return

    select col1, col2, col3 from dbo.tab1 where @parm2 = '007' and ....

    union all

    select col1, col2, col3 from dbo.tab2 where @parm2 = '006' and ....

    ...

    As long as col1, col2, and col3 are of the same data types.

  • Guys, after reviewing MSDN, SQL does not allow this type of usage inside a function. I appreciate your help.

  • ScottPletcher (5/26/2016)


    You can use derived tables within the query to provide computed values, you don't need variables.

    Also, here's a quick demo to show that you can query from different tables, as long as the results sets are compatible:

    CREATE FUNCTION dbo.test_diff_queries_in_a_function

    (

    @Usage_Date SmallDAteTime,

    @RateCode Varchar(4)

    )

    RETURNS TABLE AS

    RETURN

    SELECT t.object_id

    FROM sys.tables t

    WHERE @RateCode = 'T'

    UNIO N AL L --Edit: I can't code this correctly or my work filter rejects it as "sql injection"

    SELECT TOP (100) column_id

    FROM sys.columns c

    INNER JOIN sys.tables t ON t.object_id = c.object_id

    WHERE c.name LIKE '%id%'

    AND @RateCode = 'C'

    GO

    SELECT * FROM dbo.test_diff_queries_in_a_function (GETDATE(), 'T')

    SELECT * FROM dbo.test_diff_queries_in_a_function (GETDATE(), 'C')

    GO

    DROP FUNCTION dbo.test_diff_queries_in_a_function

    I have that problem with the word D E C L A R E.

  • Thanks Lynn, each query has different columns and widths. otherwise this would have worked,

  • fergfamster (5/26/2016)


    Thanks Lynn, each query has different columns and widths. otherwise this would have worked,

    You can always use a hard coded NULL as column placeholder when doing UNION ALL.

    select name

    , column_id

    from sys.columns

    union all

    select name

    , NULL --Just need a placeholder here

    from sys.tables

    You can adjust this type of thing to suit your requirements.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • thanks Sean, I should have thought of that.

    this works now.

  • fergfamster (5/26/2016)


    Well I gave the code at the beginning.

    here is my original with 1 inline query:

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER FUNCTION [dbo].[MyFunction](

    @Usage_Date SmallDAteTime,

    @RateCode Varchar(4)

    )

    RETURNS TABLE AS

    RETURN

    select, where

    group By ....

    My New Query Im simply trying to use an IF statement to have 4 different types of queries based on the variable value for rateCode. When trying to use an If statement I get an error like the one below stating Declare Table Scalar Variable. Its declared directly after the function name for the variables passed, however seems to not like being used with if statements or case statements other than just calling the variable name itself in my query directly.

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER FUNCTION [dbo].[MyFunction](

    @Usage_Date SmallDAteTime,

    @RateCode Varchar(4)

    )

    RETURNS TABLE AS

    RETURN

    IF @RateCode = 'O07'

    (

    select Rate=case

    Group By ...

    )

    ELSE IF @RateCode = 'O01'

    (

    select Rate=case

    Group By ...

    )

    You can't do what you're trying to do. The "RETURNS TABLE" version of UDFs is for inline-table functions. They have a very specific syntax and work a very specific way.

    You might be able to do it as a multi-statement UDF. In that case, you would Return a table variable. Looks like "RETURNS @TableName TABLE (...table variable definition...)". Create Function documentation on MSDN has the details (https://msdn.microsoft.com/en-us/library/ms186755.aspx). This will only work if the returned table has the same structure (columns, data-types, etc.) and what you want is different queries.

    Performance on multi-statement UDFs is almost always much, much worse than inline-table functions. The advantage is they allow the kind of thing you're trying to do.

    There are other options, but the particular solution you are trying to do is not something SQL Server (or Oracle/DB2/etc.) supports.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply