subject:Scalar Functions
syntax:CREATE FUNCTION [owner_name.]function_name
( @parameter [ AS ] data_type [ = default_value ] )
RETURNS data_type
[ WITH { ENCRYPTION | SCHEMABINDING } ]
[ AS ]
BEGIN
<statement>
RETURN scalar_expression
END
content:เป็นการสร้าง function เพื่อใช้ประโยชน์ต่างๆ
example:--------- ex1
CREATE FUNCTION udf_dateOnly
( @mydate smalldatetime )
RETURNS varchar(20)
AS
BEGIN
RETURN CONVERT(varchar(20), DATEPART(mm, @mydate)) + "/"
+ CONVERT(varchar(20), DATEPART(dd, @mydate)) + "/"
+ CONVERT(varchar(20), DATEPART(yyyy, @mydate))
END
GO
SELECT id, udf_dateOnly(date_born) FROM person
--------- ex2
CREATE FUNCTION udf_squareArea
( @width decimal(4,1), @height decimal(4,1) )
RETURNS decimal(8,2)
AS
BEGIN
DECLARE @sum decimal(8,2)
SET @sum = @width * @height
RETURN @sum
END
CREATE TABLE Square
(
id int PRIMARY KEY,
width decimal(4,1),
height decimal(4,1),
area AS ( udf_squareArea(width, height) )
)
--------- ex3
CREATE FUNCTION udf_totalQtyAvg()
RETURNS int
AS
BEGIN
RETURN ( SELECT AVG(quantity) FROM orders )
END
SELECT id, ( SELECT AVG(quantity) FROM orders )
FROM orders
SELECT id, udf_totalQtyAvg()
FROM orders
