Executing dynamic functions in table select

  • I mark The Dixie Flatline answer as solution, because is a good idea

    to implement it i need to add a as many columns to my holidays table as distinct parameters that i need in all functions i'll be using (about 5 new columns)

    But was not really the point, the discussion is not about calculating dates, holidays or any date manipulation

    The discussion i want is about having a column where you have a statment, or an UDF, in a form of a VARCHAR, and with an SProc have that column calculated and returned in a new column.

    Is there a way to do it, without using dynamic sql, and with no columns added to the table?

    the perfect solution will be something like:

    SELECTColumn1

    , Column2

    , ...

    , FunctionColumn

    , EXEC(FunctionColumn) AS FunctionResult

    FROMMyTable

  • ChrisM@Work (8/25/2016)


    Of course not. The function calculates US holidays because most users of ssc are in or from the US. I'm in the UK and I've written a similar function for use here. They could easily be merged.

    I don't know where in the world you are Sergiy but wherever you are, I'm sure you can do one for your country.

    That function is not good even for US:

    http://www.timeanddate.com/holidays/us/martin-luther-king-day

    http://www.cityclerk.nyc.gov/html/about/holidays.shtml

    As about me - I'm everywhere, our offices are in more than 20 counries around the world.

    Every country has its own set of holidays.

    And there are local public holidays.

    Many of big cities have their own "Day of the City" - public holiday for city residents only.

    I'm sure such a practice exists in US and UK as well.

    Sometimes public holidays get shifted, or week-end days-off around them get shifted not to interrupt a sequence of holidays with a single working day in between them.

    Governments of different levels introduce new holidays or remove them.

    How many functions do you want to create to deal with all of these cases?

    And how often you plan to deploy them to every production server where they are used?

    How do you deal with medical software, where the environment is closed and code changes must go through comprehensive testing and get approved?

    How much resources (read - money) will your function consume?

    Compare it with a single function reading the data from a single Calendar table having holiday records specific for different locations, and simple user interface which allows users update the recods in the table themselves, when local government decides to change the schedule of public holidays.

    No risk of bugs, no failing deployments, no programmers involved.

    List of public holidays is not a routine. It's knowledge base.

    Which means - data.

    Hardcoding data - it's a very bad approach.

    It's bad every time and every where.

    _____________
    Code for TallyGenerator

  • Sergiy (8/25/2016)


    ChrisM@Work (8/25/2016)


    Of course not. The function calculates US holidays because most users of ssc are in or from the US. I'm in the UK and I've written a similar function for use here. They could easily be merged.

    I don't know where in the world you are Sergiy but wherever you are, I'm sure you can do one for your country.

    That function is not good even for US:

    http://www.timeanddate.com/holidays/us/martin-luther-king-day

    http://www.cityclerk.nyc.gov/html/about/holidays.shtml

    ...

    Compare it with a single function reading the data from a single Calendar table having holiday records specific for different locations, and simple user interface which allows users update the recods in the table themselves, when local government decides to change the schedule of public holidays.

    No risk of bugs, no failing deployments, no programmers involved.

    ...

    This is exactly what I'd advocate in almost every case. It's a heck of a lot more sensible than "something more dynamic, something like a computed column with dynamic parameters". When a calendar table isn't available and isn't likely to be made available, then a locally-scoped iTVF will often suffice.

    β€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • Compare it with a single function reading the data from a single Calendar table having holiday records specific for different locations, and simple user interface which allows users update the records in the table themselves, when local government decides to change the schedule of public holidays.

    No risk of bugs, no failing deployments, no programmers involved.

    Agreed! The shipping industry have books of all the worlds holidays. They are available in machine-readable form by country and political unit. The problem is if you bring a cargo ship into a port, and nobody's working that day you have to pay for parking your super ship. It is much cheaper to keep it at sea. You have to plan your schedules so the cargo arrives when there are actually dockworkers.

    I used to post a list of the various rules classifying holidays; there is about 25 of them. Then, given all of that, as you said, a local decree adds a holiday. Frankly, trying to compute calendrical functions is a waste.

    Books in Celko Series for Morgan-Kaufmann Publishing
    Analytics and OLAP in SQL
    Data and Databases: Concepts in Practice
    Data, Measurements and Standards in SQL
    SQL for Smarties
    SQL Programming Style
    SQL Puzzles and Answers
    Thinking in Sets
    Trees and Hierarchies in SQL

  • can you go back to the point of the discussion as i ask?

    this is not about hollidays, it was a example, the focus is what i post before

    paulo.margarido (8/25/2016)


    The discussion i want is about having a column where you have a statment, or an UDF, in a form of a VARCHAR, and with an SProc have that column calculated and returned in a new column.

    Is there a way to do it, without using dynamic sql, and with no columns added to the table?

    the perfect solution will be something like:

    SELECTColumn1

    , Column2

    , ...

    , FunctionColumn

    , EXEC(FunctionColumn) AS FunctionResult

    FROMMyTable

  • The discussion I want is about having a column where you have a statement, or an UDF, in a form of a VARCHAR, and with an SProc have that column calculated and returned in a new column.

    Please look at my credentials. I can tell you with certainty that we (ANSI standards committee) never intended SQL to be used this way. SQL is a database language, whose purposes are to maintain data integrity, standardize access to the data for other languages, and allow queries to the data. It was never meant for computations or symbolic manipulationd

    I would guess that the best languages for constructing and executing functions on the fly would be LISP, Mathematica etc. .

    Books in Celko Series for Morgan-Kaufmann Publishing
    Analytics and OLAP in SQL
    Data and Databases: Concepts in Practice
    Data, Measurements and Standards in SQL
    SQL for Smarties
    SQL Programming Style
    SQL Puzzles and Answers
    Thinking in Sets
    Trees and Hierarchies in SQL

  • ok, now can we go back to the discussion?

  • EXEC(FunctionColumn) AS FunctionResult

    Sorry, but functions can't call procedures. They can only call other functions. Period.

    If I understand you correctly now, you want to be able to pass a string that is itself a calculation, and have the EXEC run that string to produce a results calculation. You aren't going to get there without doing dynamic SQL and performance is probably going to suffer some.

    Could you explain why you feel the need to do this? What is so dynamic about the calculation strings you are dealing with?

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • paulo.margarido (8/25/2016)


    ok, now can we go back to the discussion?

    You're confusing the purpose and capabilities of SQL Server with those of Excel.

    Use Excel for this.


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • The Dixie Flatline (8/25/2016)


    Sorry, but functions can't call procedures. They can only call other functions. Period.

    Any bets on that? πŸ˜‰

    --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)

  • paulo.margarido (8/25/2016)


    ok, now can we go back to the discussion?

    Here is what solves your problem.

    SELECT h.Date

    FROM dbo.Holidays h

    WHERE Territory = @Territory

    AND Level = @Level

    AND Date >= DATEADD(YY, DATEDIFF(YY, 0, @ReuestedDate-2), 0)

    AND Date < DATEADD(YY, DATEDIFF(YY, 0, @ReuestedDate+2), 0)

    If you after some functions, not the actual solution - feel free to create as many functions as you wish.

    _____________
    Code for TallyGenerator

  • i know that, was an try to explain of what i want

    The Dixie Flatline (8/25/2016)


    EXEC(FunctionColumn) AS FunctionResult

    Sorry, but functions can't call procedures. They can only call other functions. Period.

    If I understand you correctly now, you want to be able to pass a string that is itself a calculation, and have the EXEC run that string to produce a results calculation. You aren't going to get there without doing dynamic SQL and performance is probably going to suffer some.

    Could you explain why you feel the need to do this? What is so dynamic about the calculation strings you are dealing with?

    i know you cant do EXEC(FunctionColumn) AS FunctionResult in a select statment

    i even try something like this, even knowing that it may not work

    create function dbo.calc_statment_in_column (@statment varchar(100), @param1 varchar(10))

    returns varchar(100)

    as

    begin

    declare @result varchar(100)

    declare @query varchar(400)

    set @query = N'SELECT @res = ' + @statment

    EXEC sp_executesql @query

    , N'@param varchar(10), @res varchar(100) OUTPUT'

    , @param = @param1, @res = @result OUTPUT

    return @result

    end

    --and use it like this, where the functionColumn has statments like "dbo.dummyfunction(@param)"

    select column1

    , functionColumn

    , dbo.calc_statment_in_column( functionColumn, @param1 ) as functionResult

    from mytable

    and it didn't

    but is this kind of aproach that i want to know if is possible

    of corse excell does it better πŸ™‚

  • Jeff Moden (8/25/2016)


    The Dixie Flatline (8/25/2016)


    Sorry, but functions can't call procedures. They can only call other functions. Period.

    Any bets on that? πŸ˜‰

    I'd probably lose the bet. Care to show us an example, Jeff?

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Viewing 13 posts - 16 through 27 (of 27 total)

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