T-SQL Table-Valued Function Example, 資料表值函數範例

Simple_Table_Valued_Function_Example.sql

-- =============================================
-- 語法簡化版的資料表值函數範例
-- =============================================
CREATE FUNCTION [dbo].[My_Simmple_Table_Valued_Function] 
(
	-- Parameters here
	@ARN VARCHAR(23)
)
RETURNS TABLE -- 可省去宣告輸出格式的動作
AS
RETURN  (

-- query SQL	
SELECT [TICKET_NUMBER] = ISNULL(RTRIM(TICKET_NUMBER),'')
      ,[PURCHASE_ORDER_NUMBER] = ISNULL(RTRIM(PURCHASE_ORDER_NUMBER),'')
      ,[CARD_NUMBER]
      ,[AMOUNT]
      ,[CATEGORY_CODE]
      ,[EFFECTIVE_DATE] = Convert(varchar(10), [EFFECTIVE_DATE], 111)
      ,[AUTHORIZATION_CODE]
  FROM DB01.dob.Table01
  WHERE ISNULL(RTRIM(TICKET_NUMBER),'') + ISNULL(RTRIM(PURCHASE_ORDER_NUMBER),'') LIKE @ARN -- Query Condition

)

Table_Valued_Function_Example.sql

-- =============================================
-- 資料表值函數範例
-- ref → http://www.codeproject.com/Articles/167399/Using-Table-Valued-Functions-in-SQL-Server
-- =============================================
CREATE FUNCTION [dbo].[My_Table_Valued_Function_Example] 
(
	@CARD_NO VARCHAR(16)
)
RETURNS 
@RESULT_TABLE TABLE -- 預先宣告輸出格式
(
	CARD_NO VARCHAR(16),
	HOLDER_NAME NVARCHAR(50), 
	HOLDER_IDN VARCHAR(20),
	PHONE_O_RRE VARCHAR(4),
	PHONE_O VARCHAR(20), 
	PHONE_H_PRE VARCHAR(4),
	PHONE_H VARCHAR(20),
	CREDIT_LIMIT MONEY -- Credit Limit(K)
)
AS
BEGIN
	--# prd data
	DECLARE @CARD_NBR NVARCHAR(19)
	SET @CARD_NBR = '000' + @CARD_NO;;

	INSERT INTO @RESULT_TABLE 
	SELECT TOP 1
	  [CARD_NO] = SubString(A.CARD_NBR, 4, 16)
	, [HOLDER_NAME] = RTrim(B.CHI_NAME)
	, [HOLDER_IDN] = RTrim(A.NID_PrimaryCard)
	, [PHONE_O_RRE] = ''
	, [PHONE_O] = RTrim(B.HOME_PHONE_2)
	, [PHONE_H_RRE] = ''
	, [PHONE_H] = RTrim(B.HOME_PHONE_1)
	, [CREDIT_LIMIT] = C.CREDIT_LIMIT / 1000 -- 單位(K)
	FROM DB01.dbo.Table01 A
	INNER JOIN DB02.dbo.Table02 B ON A.NID_PrimaryCard = B.NAME_ADDRESS_ACCT_NO
	INNER JOIN DB03.dbo.Table03 C ON B.NAME_ADDRESS_ACCT_NO = C.CUSTOMER_NBR
	WHERE A.CARD_NBR = @CARD_NBR;

	RETURN

	--## data for testing =============================================================
	-- 在未知正式的資料來源狀況下，可以先填測試用的資料
	--INSERT INTO @RESULT_TABLE VALUES (@CARD_NO, 'HOLDER_NAME', 'IDN1234567', '0800', '1234567890', '0900','1234567899',883);
	--INSERT INTO @RESULT_TABLE VALUES (@CARD_NO, 'HOLDER_NAME', 'IDN1234567', '0800', '1234567890', '0900','1234567899',883);
	--INSERT INTO @RESULT_TABLE VALUES (@CARD_NO, 'HOLDER_NAME', 'IDN1234567', '0800', '1234567890', '0900','1234567899',883);
	--RETURN
END