Thursday, November 4, 2010

Scalar and Inline Table Functions

After reading some articles on TSQL, I made some experiments on User defined functions. Actually, there are three types of UDF. First we have the Scalar functions that returns a single value of the data type referenced in the RETURNS clause of the CREATE FUNCTION statement. The returned data can be of any type except text, ntext, image, cursor, or timestamp. Next we have Inline table-valued function which returns a variable of data type table whose value is derived from a single SELECT statement. Lastly, we have multi-statement table-valued function which is slightly more complicated than the other two types of functions because it uses multiple statements to build the table that is returned to the calling statement.
For the sake of simplicity, I'll tackle the first two types of functions.

1. Scalar function example:
1:  create function ComputeSum(@FirstNum int, @SecondNum int)  
2:  returns int  
3:  as  
4:  begin  
5:  Declare @sum int  
6:  set @sum = (@FirstNum + @SecondNum)  
7:  return @sum  
8:  end  

Note: As you can see, the function takes two arguments of type int. Then on the function body, the computation proceeds, then calls the return statement.

--to execute function
 declare @result int  
 set @result = dbo.ComputeSum(5,10)  
 print @result  

15 will be displayed as output

2. Inline Table Functions (make sure you have adventureworks db in your sql server)
create function maxorder()
returns table
return select max(SalesOrderID) as LastOrderID
from Sales.SalesOrderHeader

--execute the function
select * from maxorder()

The output returned is: 75123 which is the latest order generated.


Post a Comment