[SQL Server] 建立Table Function

2017-12-15

23623
0
2018-01-02

SQL建立Table Function運用

====Step1====
先建立表單格式至資料表類型，供Function或Stored Procedure宣告使用
位置：可程式性->類型->使用者定義資料表類型->新增

Create Type [TableTypeName] as Table
(
	[Column_A] int null,
	[Column_B] varchar(20) null,
	[Column_C] decimal(18,0) null,
)

====Step2====
在Table Function宣告定義之Table和變數後，在Function編寫Select將資料塞入

Create Function [FnName]
(
	@A int,
	@B varchar(20),
	@C decimal(18,0),
	@MyTable TableTypeName readonly
)
Returns Table
as
Return
(
    Select
    	[Column_A],
    	Case 
	    When [Column_A]=@A
	    then isnull
             (
    		   (Select [Column_B] From @Mytable as [TableA]
	     	   Where [TableA].[Column_C]=[TableB].[Column_C]),'nulltext'
    	     )
	    else [Column_B] as [Column_B],
	    [Column_C]*[Column_A] as [Column_C]
	From @MyTable TableB
	Where [Column_C]>=@C and [Column_B] Like '%'+@B+'%'
	--Group by第1到4字相同者
	Group By Substring([Column_B],1,4) 
)

====Step3====
寫Stored Procedure呼叫Table Function來使用囉～

Declare
	@AA int,
	@BB varchar(20),
	@CC decimal(18,0),

set @AA=123
set @BB='A4567'
set @CC=8.9

Select * From [FnName](@AA, @BB, @CC)
Union
Select * From [FnName](@AA+1, @BB, @CC+1)

回首頁