Sql UDFs

A User defined function is a transact-sql or CLR routine that accepts parameters, performs some action and returns result of action as a value, returned value can either be a scalar(single) value or a table.
When can be used
1. Replacing stored procedure.
2. To parameterize a view.
3. In T-sql like select statement.
4. Inside another UDF.
5. In applications calling the function.

Types of UDFs
1. Scalar functions
2. Inline table-valued functions
3. Multi-statement table-valued functions

1. Scalar function - Returns single value.
Ex.
create function ProjectEstimation(@developmentDays int, @testingDays int)
returns int
as
begin
declare @totalDays int;
select @totalDays = @developmentDays + @testingDays;
return @totalDays
end
--calling 
select ProjectEstimation(50, 26)

2. Inline table-valued function - Returns a table as result, no need to declare table structure.
Ex.
create function EmpWiseCityCollection(@emp varchar(20))
returns table as
return
(
select city, sum(Collection) [Collection] from tbl_employee where empName = @emp group by city
);
--calling 
select * from EmpWiseCityCollection('mark')

3. Multi-statement table-valued functions - Returns a table as result, need to declare table structure.
Ex.
CREATE FUNCTION SalesByPerson(@sales_ID int)
RETURNS @SalesData TABLE
(
[CustomerID] int,
[SalesAmount] int,
[SalesDate] date
)
AS
BEGIN
INSERT INTO @SalesData
SELECT Sales.CustomerID, Sales.SalesAmount, Sales.SalesDate from Sales
Where Sales.Sales_ID = @sales_ID
RETURN
END
--calling select * from  SalesByPerson (1002)


Why use multi-statement table-valued functions instead of inline table-valued functions?
1) Generally, we use multi-statement table-valued functions when we need to perform further operations (for example, inserts, updates, or deletes) on the contents of the table variable before returning a result set.
2) We would also use them if we need to perform more complex logic or additional processing on the input parameters of the function before invoking the query to populate the table variable.

EXCEPT and INTERSECT

Except - It returns data from left side query expression that is not in the data by right side query expression.
Ex.

A - data that is not in right side query result
B - data that is not in left side query result
C - data that is common in both results

Except returns A. 

Intersect - It returns data that is common in left and right side query expressions.
Intersect returns C.

Syntax -

{ <query_specification> | ( <query_expression> ) } 
{ EXCEPT | INTERSECT }
{ <query_specification> | ( <query_expression> ) }