TSQL x 具參數的『多重陳述資料表值』函數 x FUNCTION

繼上次『具參數內嵌資料表值』函數  FUNCTION 後，今天剛好用到『多重陳述資料表值函數』就順便紀錄、複習一下其他種SQL-SERVER的函數

1.純量值函數
2.內嵌資料表值函數
3.多重陳述資料表值函數

1. 純量值函數 SCALAR FUNCTION

CREATE FUNCTION 年資 (@開始工作時間,@現在or結束工作時間)
RETURNS MONEY
AS
BEGIN

     DECLARE @結果 AS MONEY

     SET @結果 = YEAR(@開始工作時間) - YEAR(@現在or結束工作時間)

     RETURN @結果

END
;

2.內嵌資料表值函數  INLINE TABLE-VALUED FUNCTION

CREATE FUNCTION 員工資料 (@INPUT_部門)
RETURNS TABLE
AS
RETURN
(
     SELECT 部門,員編,姓名,性別,職務
          FROM 員工基本檔
               WHERE 部門 = @INPUT部門
)
;

3.多重陳述資料表值函數 MULTISTATEMENT TABLE-VALUED FUNCTION

CREATE FUNCTION 誰是代理人 (@INPUT_部門,@INPUT_員工姓名)
RETURNS @回傳的TABLE TABLE
(
    姓名 varchar(10),
    電話 varchar(10)
)
AS
BEGIN
     DECLARE  @暫存欄位  varchar(10);

     SELECT @暫存欄位 = 代理人
          FROM 員工基本檔
               WHERE 部門=@INPUT_部門
                AND 員工姓名 = @INPUT_員工姓名

     INSERT @回傳的TABLE
         SELECT  姓名,電話
              FROM 員工基本檔
                   WHERE 員工姓名=@暫存欄位
RETURN
END
;

TSQL x 具參數的『內嵌資料表值』函數 x Function

因為公司ERP系統是跟外面買的(非自行開發)，現在如果要報表產生程式中沒有廠商設定好到的欄位，該怎麼辦

我第一個想到的就是直接再報表裡面寫CommandText丟SQL進去撈資料，但報表的程式碼看起來非常大一串

後來前輩叫我用SQL-SERVER的Function，想說T-SQL不熟就來練練吧

具參數的"內嵌資料表值"函數 (INLINE TABLE-VALUED FUNCTION)

CREATE FUNCTION INVTEST (@A varchar(20),
                                                        @B varchar(20),
                                                        @C varchar(20))
RETURNS TABLE
AS
RETURN
SELECT X.column1 ,  
                Y.column1 ,  
                CAST(ISNULL(X.column2,0) AS numeric(10,2)) ,        
                CAST(ISNULL(Y.column2,0) AS numeric(10,2))      
FROM TABLE_NAME_1 X            
LEFT JOIN TABLE_NAME_3 Y      
ON ...          
AND ...
WHERE X.column5=@A        
      AND X.column6=@B
      AND X.column7=@C
UNION ALL                      
SELECT X.column ,  
                Y.column ,  
                CAST(ISNULL(X.column,0) AS numeric(10,2)) ,                
                CAST(ISNULL(Y.column,0) AS numeric(10,2))              
FROM TABLE_NAME_2 X            
LEFT JOIN TABLE_NAME_3 Y      
ON ...          
AND ...
AND ...
WHERE X.column5=@A        
      AND X.column6=@B
      AND X.column7=@C  

--CAST(ISNULL(X.column2,0) AS numeric(10,2)) ,
-- 處理如果X.column2是空值，就帶0，再將處理完的值轉成數值 : 整數位8碼，小數位2碼    
--UNION ALL上面跟下面的主要TABLE不一樣
--@A , @B , @C 要丟進去查詢的值
;

執行
SELECT * FROM dbo.INVTEST(A,B,C)

TSQL x 日期時間的顯示方法 x Time

以前使用Oracle，如果要篩選時間資料的話，只要下to_char('欄位名稱','yyyymmdd')

或是 to_date('欄位名稱','yyyymmdd') 就可以了

'yyyymmdd' 可以換成 'yyyy-mm-dd' 、'yyyy/mm/dd' 產生出不同的樣式出來

在T-SQL中，這種語法變成，CONVERT(CHAR(8),欄位名稱,112) 

CONVERT語法最後一個涵式是 "日期格式" (上述的 112)

112 : yyyymmdd   舉例 : 20160101

111 : yyyy/mm/dd  舉例 : 2016/01/01

102 : yyyy.mm.dd  舉例 : 2016.01.01

120 : yyyy-mm-dd  舉例 : 2016-01-01

如果要有符號   /.-  的話長度就要設定char(10)

若只有char會顯示時分秒 >> CONVERT(CHAR,欄位名稱,112)

所以 Oracle : to_date('日期欄位','yyyymmdd')  =  MSSQL : CONVERT(char(8),日期欄位,112)

我是參考保哥的  善用 SQL Server 中的 CONVERT 函數處理日期字串

其他格式或相關資料可以參考 MSDN 的 CAST 和 CONVERT (Transact-SQL)