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

sql-server tsql user-defined-functions
Francesca picture Francesca · Jul 2, 2010 · Viewed 13.7k times · Source

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.

Answer

Ben M picture Ben M · Jul 2, 2010

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

Related questions

Multi-statement Table Valued Function vs Inline Table Valued Function

A few examples to show, just incase: Inline Table Valued CREATE FUNCTION MyNS.GetUnshippedOrders() RETURNS TABLE AS RETURN SELECT a.SaleId, a.CustomerID, b.Qty FROM Sales.Sales a INNER JOIN Sales.SaleDetail b ON a.SaleId = b.SaleId INNER …

Read More
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.Splitfn", or the name is ambiguous

Hai guys, I ve used the following split function, CREATE FUNCTION dbo.Splitfn(@String varchar(8000), @Delimiter char(1)) returns @temptable TABLE (items varchar(8000)) as begin declare @idx int declare @slice varchar(8000) select @idx = 1 if len(@String)<1 or @String is null …

Read More
How to report an error from a SQL Server user-defined function

I'm writing a user-defined function in SQL Server 2008. I know that functions cannot raise errors in the usual way - if you try to include the RAISERROR statement SQL returns: Msg 443, Level 16, State 14, Procedure ..., Line ... Invalid use of a side-effecting …

Read More