Scalar Value Function
A Scalar-valued function in SQL Server 2012 is used to return a single value of any T-SQL data type.
CREATE FUNCTION dbo.Calculate(@val1 int, @val2 int)
RETURNS int
AS
BEGIN
DECLARE @val3 int;
SELECT @val3 = @val1 + @val2;
RETURN @val3;
END
Multi Table Value Function
1. Declare a table variable that is to be returned.
2. Must have a BEGIN/END block.
3. Inside the BEGIN/END block you need code that populates the table variable.
4. You return from the function.
CREATE FUNCTION Util.MyMTVFunction (@Parameters INT)
RETURNS @FunctionResultTableVariable TABLE (N INT)
AS
BEGIN
INSERT INTO @FunctionResultTableVariable (N)
SELECT column_name1,column_name2,...
FROM table_name
ORDER BY column_name ase|desc;
RETURN;
END
GO
In-line Table Value Function
1. Returns a select statement – there is no table variable to mess around with, no inserts, no code blocks.
CREATE FUNCTION Util.MyITVFunction (@Parameters INT)
RETURNS TABLE
AS
RETURN
SELECT column_name1,column_name2,...
FROM table_name
ORDER BY column_name ase|desc;
GO
Tech Corner 