CODEWITHSUNDEEP
sql-servert-sqluser-defined-functions
Francesca
Francesca

Reputation: 21640

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

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.

Upvotes: 32

Views: 17443

Answers (2)

OMG Ponies
OMG Ponies

Reputation: 332581

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

Upvotes: -1

Ben M
Ben M

Reputation: 22492

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

Upvotes: 14

Related Questions