Executing Functions From Table Column

  • I have a table with each row is assigned with a scalar function that returns a nvarchar value.

    I have created a sqlfiddle to help understand my structure and data. I have tried various ways to get the output of the function stored in the last column but could not do.

    Instead of

    SELECT DashboardID, Name, FunctionName FROM tblDashboard

    I'm looking for

    SELECT DashboardID, Name, <output of function in FunctionName> FROM tblDashboard

    I could think of achieving this using cursors but wanted to check with experts here as the last chance to check for other solutions.

  • there's no information on the structure of your function. What does your function do?

    Not sure what you mean by "executing functions from Table column"... The solution to this might be really simple, and you many not need a function at all - and if you don't have to use one, don't do it. Regular set-based T-SQL will run circles around scalar functions any day of the week.

    Please explain what the input parameters to your function are, and how you're calculating the output. You may not need the function at all, or if you do, you could return a table-valued function or something similar...

  • If you just want the static result of a function call to be inserted to the table, use INSERT INTO ... SELECT rather than INSET INTO ... VALUES:

    INSERT INTO tblDashboard (DashboardId, Name, Description, FunctionName)

    SELECT 1, 'Function1', 'Function1', dbo.fnFunction1()

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

  • sarath.tata (4/13/2016)


    I have a table with each row is assigned with a scalar function that returns a nvarchar value.

    I have created a sqlfiddle to help understand my structure and data. I have tried various ways to get the output of the function stored in the last column but could not do.

    Instead of

    SELECT DashboardID, Name, FunctionName FROM tblDashboard

    I'm looking for

    SELECT DashboardID, Name, <output of function in FunctionName> FROM tblDashboard

    I could think of achieving this using cursors but wanted to check with experts here as the last chance to check for other solutions.

    I think you're talking about a "computed column". Here's one way to do it with the understanding that, when it comes to making changes in the future, this is the stuff of nightmares.

    /*

    DROP TABLE [dbo].[tblDashboard]

    DROP FUNCTION [dbo].[fnFunction1],[dbo].[fnFunction2]

    */

    Go

    CREATE FUNCTION [dbo].[fnFunction1]

    (

    )

    RETURNS VARCHAR(20) WITH SCHEMABINDING

    AS

    BEGIN

    RETURN 'Function1'

    END

    GO

    CREATE FUNCTION [dbo].[fnFunction2]

    (

    )

    RETURNS VARCHAR(20) WITH SCHEMABINDING

    AS

    BEGIN

    RETURN 'Function2'

    END

    GO

    CREATE TABLE [dbo].[tblDashboard]

    (

    [DashboardID] [int] NOT NULL,

    [Name] [nvarchar](255) NOT NULL,

    [Description] [nvarchar](2047) NOT NULL,

    [FunctionName] AS (CASE WHEN Name = 'Function1' THEN dbo.fnFunction1() WHEN Name = 'Function2' THEN dbo.fnFunction2() ELSE NULL END) PERSISTED,

    CONSTRAINT [PK_Dashboard]

    PRIMARY KEY CLUSTERED ([DashboardID] ASC)

    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,

    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,

    ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    INSERT INTO tblDashboard (DashboardId, Name, Description)

    VALUES (1, 'Function1', 'Function1')

    GO

    INSERT INTO tblDashboard (DashboardId, Name, Description)

    VALUES (2, 'Function2', 'Function2')

    GO

    SELECT * FROM dbo.tblDashboard

    I'll also strongly recommend against using "tbl" and "fn" and other forms of Hungarian Notation. For me personally, it's a "code smell".

    Last but not least, learn how to post code on this forum. I went to SQLFiddle to help you but it's not just you that could benefit and there's no guarantee that your post there will persist forever. I believe you also find that people tend to shy away from links to other places.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I may not have conveyed my question properly. This is how I achieved my solution. I understand my code references tbl and fn but I have to code like that as that is how the standards in my organization were setup though I'm personally not inclined to do that way.

    CREATE FUNCTION [dbo].[fnFunction1]

    (

    )

    RETURNS VARCHAR(20)

    AS

    BEGIN

    RETURN 'My Function1 Output'

    END

    GO

    CREATE FUNCTION [dbo].[fnFunction2]

    (

    )

    RETURNS VARCHAR(20)

    AS

    BEGIN

    RETURN 'My Function2 Output'

    END

    GO

    CREATE FUNCTION [dbo].[fnFunctionWrapper] (@id INT)

    RETURNS VARCHAR(20)

    AS

    BEGIN

    DECLARE @output VARCHAR(20)

    IF @id = 1

    set @output = dbo.fnFunction1()

    ELSE IF @id = 2

    set @output = dbo.fnFunction2()

    return @output

    END

    GO

    CREATE TABLE [dbo].[tblDashboard]

    (

    [DashboardID] [int] NOT NULL,

    [Name] [nvarchar](255) NOT NULL,

    [Description] [nvarchar](2047) NOT NULL,

    [FunctionName] [nvarchar](511) NULL,

    CONSTRAINT [PK_Dashboard]

    PRIMARY KEY CLUSTERED ([DashboardID] ASC)

    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,

    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,

    ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    INSERT INTO tblDashboard (DashboardId, Name, Description, FunctionName)

    VALUES (1, 'Function1', 'Function1', 'dbo.fnFunction1()')

    GO

    INSERT INTO tblDashboard (DashboardId, Name, Description, FunctionName)

    VALUES (2, 'Function2', 'Function2', 'dbo.fnFunction2()')

    GO

    SELECT DashboardID, Name, dbo.fnFunctionWrapper(DashboardID) FROM tblDashboard

  • UDFs (Scalar and Multi-statement Table Valued Functions) are UNBELIEVABLY BAD!!!! If you want some details, grab a copy of the SQL Server MVP Deep Dives 2 book (proceeds go to charity) and read my chapter entitled "Death by UDF". It's the best chapter in the book!! 😎

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • sarath.tata (4/14/2016)


    I may not have conveyed my question properly. This is how I achieved my solution. I understand my code references tbl and fn but I have to code like that as that is how the standards in my organization were setup though I'm personally not inclined to do that way.

    CREATE FUNCTION [dbo].[fnFunction1]

    (

    )

    RETURNS VARCHAR(20)

    AS

    BEGIN

    RETURN 'My Function1 Output'

    END

    GO

    CREATE FUNCTION [dbo].[fnFunction2]

    (

    )

    RETURNS VARCHAR(20)

    AS

    BEGIN

    RETURN 'My Function2 Output'

    END

    GO

    CREATE FUNCTION [dbo].[fnFunctionWrapper] (@id INT)

    RETURNS VARCHAR(20)

    AS

    BEGIN

    DECLARE @output VARCHAR(20)

    IF @id = 1

    set @output = dbo.fnFunction1()

    ELSE IF @id = 2

    set @output = dbo.fnFunction2()

    return @output

    END

    GO

    CREATE TABLE [dbo].[tblDashboard]

    (

    [DashboardID] [int] NOT NULL,

    [Name] [nvarchar](255) NOT NULL,

    [Description] [nvarchar](2047) NOT NULL,

    [FunctionName] [nvarchar](511) NULL,

    CONSTRAINT [PK_Dashboard]

    PRIMARY KEY CLUSTERED ([DashboardID] ASC)

    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,

    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,

    ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    INSERT INTO tblDashboard (DashboardId, Name, Description, FunctionName)

    VALUES (1, 'Function1', 'Function1', 'dbo.fnFunction1()')

    GO

    INSERT INTO tblDashboard (DashboardId, Name, Description, FunctionName)

    VALUES (2, 'Function2', 'Function2', 'dbo.fnFunction2()')

    GO

    SELECT DashboardID, Name, dbo.fnFunctionWrapper(DashboardID) FROM tblDashboard

    Ah... I see. Kevin is absolutely correct. Scalar and mTVF functions are really bad for performance and functions calling functions are the worst from many different aspects but particularly where performance comes into play.

    iTVFs (INLINE table valued functions) are a whole lot better (usually at least 7 times better even for simple stuff). Please see the following article on how to simulate an iSF (high performance INLINE scalar function).

    http://www.sqlservercentral.com/articles/T-SQL/91724/

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I totally agree with you. Initially they were all Stored Procedures. I changed them to functions as I can't really do EXEC in select query unless I write some complex logic of going through each row and capturing result in a temp table.

    I understand this is a performance hit using Scalar functions. These functions have a complex logic built-in using / interacting with multiple tables and producing a HTML in varchar to render on UI.

    I have decided to use this way as I will never have more than 10 rows in that table and not all of them would be executed at once as we have further filtering in that table to decide which user will have access to which Dashboard item.

  • sarath.tata (4/14/2016)


    I totally agree with you. Initially they were all Stored Procedures. I changed them to functions as I can't really do EXEC in select query unless I write some complex logic of going through each row and capturing result in a temp table.

    I understand this is a performance hit using Scalar functions. These functions have a complex logic built-in using / interacting with multiple tables and producing a HTML in varchar to render on UI.

    I have decided to use this way as I will never have more than 10 rows in that table and not all of them would be executed at once as we have further filtering in that table to decide which user will have access to which Dashboard item.

    Do visit the article I linked to so that you can still take advantage of the functions like you want but with a whole lot better performance. Read the whole article so that you understand what a difference the simulated iSFs can make.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 9 posts - 1 through 8 (of 8 total)

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