Code (UDF-Tutorial)
Uploaded by
hagertamer1620Code (UDF-Tutorial)
Uploaded by
hagertamer1620-- Aggrigate functions
Select getdate(); -- Non-deterministic function.
Select YEAR(getdate()); -- 'Year' is deterministic function.
Select YEAR(getdate()) as 'This Year', getdate() as 'Now';
Select convert(date, getdate()) dateOnly;
Select convert(time, getdate());
SELECT CONVERT(VARCHAR(8),GETDATE(), 3);
-- Creating a simple UDF
-- http://www.sqlteam.com/article/intro-to-user-defined-functions-updated
-- The following statement shows how to create a function that accepts
-- two input parameters, sums them together and then
-- returns the sum to the calling statement.
if object_id('udfSumTwoValues') is not null
drop function dbo.udfSumTwoValues;
Go
CREATE FUNCTION udfSumTwoValues( @Val1 int, @Val2 int )
RETURNS int
AS
BEGIN
RETURN (@Val1+@Val2)
END
-- execute the function.
SELECT dbo.udfSumTwoValues(55,22) AS Sum1is;
SELECT dbo.udfSumTwoValues(10.5,3.22) AS Sum2Is;
SELECT dbo.udfSumTwoValues('2','5') AS Sum3Is;
SELECT dbo.udfSumTwoValues('10',3.22) AS Sum4Is;
SELECT dbo.udfSumTwoValues('10','3.22') AS Sum5Is;
SELECT dbo.udfSumTwoValues('5', 'b') AS Sum6Is;
------------------------------------------------------
-- @ Scalar function.
------------------------------------------------------
-- http://www.sqlservercurry.com/2014/03/user-defined-functions-in-sql-server_20.html
USE northwind
GO
Select * From Customers
Select * From Employees
Select * From Orders
where OrderId = '10248'
Select * from [Order Details]
where OrderId = '10248'
Select sum(UnitPrice * Quantity) from [Order Details]
where OrderId = '10248'
Select * From Products
GO
-- Scalar Function
---------------------------------------------------------------
-- Feach the total number of orders placed by a given customer.
drop function dbo.fn_name
create function fn_name()
returns Int
Begin
return ( select 2)
End
select dbo.fn_name() as 'Result';
Create Function FetchTotalOrders( @p_CustomerID NVarChar(10) )
Returns Int
Begin
Return
(
Select Count(OrderID)
From Orders
Where CustomerID = @p_CustomerID
)
End;
-- Test the function
Select dbo.FetchTotalOrders('alfki') as 'Total Number Of Orders';
Select * from customers;
Select dbo.FetchTotalOrders('Arout') as 'Total Number Of Orders';
Select dbo.FetchTotalOrders('BCD') as 'Total Number Of Orders';
------------------------------------------------------------------
-- another example which will fetch the number of orders
-- processed by an employee for a given year.
Create Function FetchEmployeeProcessedOrdersYearWise
(
@p_EmployeeID Int,
@p_Year Int
)
Returns Int
Begin
Return
(
Select Count(OrderID)
From Orders
Where EmployeeID = @p_EmployeeID
And Year(OrderDate) = @p_Year
)
End;
-- Test Function
Select dbo.FetchEmployeeProcessedOrdersYearWise(1, 1996) as 'Year 1996'
union
Select dbo.FetchEmployeeProcessedOrdersYearWise(1, 1997) as 'Year 1997'
union
Select dbo.FetchEmployeeProcessedOrdersYearWise(1, 1998) as 'Year 1998';
---------------------------------------------------------------------------
-- InLine Table valued functions
---------------------------------------------------------------------------
-- The following uses the Customer table in the Northwind database
-- to show
-- how an inline table-valued function is implemented.
USE Northwind
go;
drop function fx_Customers_ByCity
CREATE FUNCTION fx_Customers_ByCity
( @dept_name nvarchar(15) )
RETURNS table
AS
RETURN (
SELECT name
FROM student
WHERE dept_name =@dept_name
)
go
select * from student
SELECT * FROM fx_Customers_ByCity('Comp. Sci.')
-- *************************************************