Executing dynamic functions in table select

Question

Executing dynamic functions in table select

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

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

paulo.margarido Ten Centuries Points: 1245 More actions · Answer 1

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

Sergiy SSC Guru Points: 110208 More actions · Answer 2

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

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

Joe Celko SSCertifiable Points: 5749 More actions · Answer 4

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

paulo.margarido Ten Centuries Points: 1245 More actions · Answer 5

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

Joe Celko SSCertifiable Points: 5749 More actions · Answer 6

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

paulo.margarido Ten Centuries Points: 1245 More actions · Answer 7

paulo.margarido

Ten Centuries

Points: 1245

August 25, 2016 at 10:24 am

#1897549

ok, now can we go back to the discussion?

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 8

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

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 9

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]

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 10

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)

Sergiy SSC Guru Points: 110208 More actions · Answer 11

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

paulo.margarido Ten Centuries Points: 1245 More actions · Answer 12

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 🙂

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 13

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