Is it possible to define a local function in a TSQL query?

Question

I have a complex expression calculating a value from a date that I have to use on multiple date columns.
Can I define a temporary local function in my query to avoid copy and pasting this expression. ?

like:

create MyLocalFunc(@ADate datetime) 
returns int as
begin
  blablabla
end

select
  MyLocalFunc(col1), col2, MyLocalFunc(col3), col4, MyLocalFunc(col5)
from
  mytable

As a workaround, I know I can do a CREATE FUNCTION // DROP FUNCTION, but I'd prefer avoid it.

See: http://stackoverflow.com/questions/981451/can-i-create-a-one-time-use-function-in-a-script-or-stored-procedure — Weasle, Dec 02 '15 at 22:57
Does this answer your question? [Can I create a One-Time-Use Function in a Script or Stored Procedure?](https://stackoverflow.com/questions/981451/can-i-create-a-one-time-use-function-in-a-script-or-stored-procedure) — a_hardin, Jan 11 '21 at 13:59

score 14 · Accepted Answer · answered Jul 02 '10 at 19:46

14

No, there is no way -- create/drop is the only choice.

answered Jul 02 '10 at 19:46

Ben M

22,262
3
67
71

1

This answer mentions a way in which it is possible: http://stackoverflow.com/a/981461/750216 – Răzvan Flavius Panda May 18 '16 at 12:07
@RăzvanFlaviusPanda the answer you linked to suggests the create/drop approach, which this answer here already mentioned. – julealgon Oct 19 '18 at 22:07

score -1 · Answer 2 · answered Jul 02 '10 at 20:01

-1

SQL Server supports anonymous blocks by putting the query(ies) inside a BEGIN/END block, but I haven't been able to find an example where someone defined a function within it. Oracle's had the functionality for a while...

answered Jul 02 '10 at 20:01

OMG Ponies

325,700
82
523
502

1

Could you explain more about anonymous blocks in SQL Server? That link is broken and I can't find anything myself. – Protector one Aug 10 '16 at 09:00

Is it possible to define a local function in a TSQL query?

2 Answers2