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)