Scalar Functions SQL Server User Defined Function : สอน คำสั่ง ของ Scalar Functions, เรียน คำสั่ง ของ Scalar Functions
 
  • Scalar Functions Scalar Functions
  • 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

       

0 Comment

  • Have no comment.

Comment Reference

 
 
 
Share This Topic Login with Facebook