MSSQL tsql stored procedure 預存程序和函式 常用語法筆記 (暫存表、宣告變數、if條件式)

簡介

預存程序(stored procedure) 是在資料庫預先擺放程式碼，以便外部程式呼叫時，可以在資料庫端先做運算，運算後再取出資料。這樣的作法有一個不可取代的好處，如果原本要運算的資料量很大，但經過運算後可以只要一點點結果，那就可以利用預存程序先行運算把資料減少，這樣傳輸到外部程式消耗的頻寬也會變少。

將查詢到的結果寫入暫存資料表 temptable

SELECT * INTO #temptable FROM TestTable -- 複製Table的結構至#temptable
SELECT * FROM #temptable --- 取出資料
DROP TABLE #temptable --- 刪除暫存的資料表

查詢到的結果寫入暫存資料表 temptable，同時附帶 join、 where 等條件參數

這個範例是只取 Left join 左側並且排除中間重疊部分的語法，見下圖

SELECT [ID],[Name]
INTO #temptable
FROM [MyTable1]
LEFT JOIN [MyTable1] ON [MyTable1].[ID] = [MyTable2].[ID]
WHERE [MyTable2].[ID] IS NULL;

更多 join 類的經典範例請見 Visual Representation of SQL Joins

---

暫存資料表 temptable 前面的符號有分成 # ## @ 三種分別是什麼意思?

前面提到 DROP TABLE #temptable ，是刪除暫存的資料表，#和@符號分別代表存位置不同

• 一個#和兩個##，只要有# 符號，資料就是存在硬碟，與資料庫連線session中斷時會刪除，也可以自己手動下drop刪除
• 兩個 # 的 ##Table 是全域的資料表，跨資料庫也可以讀取資料
• 一個@ 資料存在記憶體，指令執行結束就會自動刪除，不需手動drop

預存程序、資料表值函式、純量值函式差別

預存程序不一定要回傳結果 (可能只是在資料庫動態新增資料)
資料表值函式和純量值函式一定會回傳結果，只是差在回傳一個table還是一個變數而已

呼叫預存程序的方式是

exec 預存程序名稱 @變數名稱1 ='變數內容1',@變數名稱2 ='變數內容2'

實際用起來像這樣

exec dbo.SearchHtml @SearchString ='%關鍵字%',@Category = 2

呼叫資料表值函式

這邊就跟預存程序不一樣了，因為是資料表的概念，所以直接用 select 即可

select* from 資料表值函式名稱('變數','變數')

建立預存程序

預存程序 PROCEDURE (Stored procedure) 不需要宣告回傳的型態，可以有回傳執行結果，也可以是單純insert不做回傳，宣告方式如下

CREATE PROCEDURE 預存程序名稱
	-- 可以傳入的變數定義寫在這裡
	@SearchString nvarchar(MAX)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    SELECT * FROM table
END

建立資料表值函式、純量值函式

這兩個函式都一定會有回傳值，所以 Create 完，下面有一個 RETURNS 要寫回傳的型態，資料表值函式就是回傳一個TABLE(類似普通sql語法查詢結果)，純量值函式則是特定一個變數，可以是字串也可以是數值

CREATE FUNCTION 函式名稱
(
	-- 可以傳入的變數定義寫在這裡
	@變數名稱 變數型態
)
RETURNS NVARCHAR(MAX) 
AS
BEGIN

END

範例

CREATE FUNCTION [dbo].[GetTreeNodeUnitName]
(
	-- 可以傳入的變數定義寫在這裡
	@NowPage int,
	@UserName NVARCHAR(20)
)
RETURNS NVARCHAR(MAX) 
AS
BEGIN

select @UnitName = a.UserName from table a where a.User = @UserName and a.Page > @NowPage

END

---

宣告變數 DECLARE

格式是

DECLARE @變數名稱 變數型態(參數)

例如

DECLARE @UserName VARCHAR(30); --宣告變數名稱UserName，型態為字串，長度30

常見的變數型態

DECLARE @SqlVariable NCHAR(4);  --固定長度4
DECLARE @SqlVariable NVARCHAR(50);  --變動長度50
DECLARE @SqlVariable NVARCHAR(MAX);  --長度設 max 可存到 2GB (同 text/ntext 的長度)
DECLARE @SqlVariable INT; --整數
DECLARE @SqlVariable decimal(5,2); --總長度5位數，小數點以後佔2位，如123.45
DECLARE @SqlVariable datetime; --日期時間
DECLARE @SqlVariable UNIQUEIDENTIFIER; 

UNIQUEIDENTIFIER 就是C#的 GUID，格式會是這樣 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx ，其中每一個 x 是範圍 0-9 或 a-f 的十六進位數字。 例如，6F9619FF-8B86-D011-B42D-00C04FC964FF。

---

使用if else條件判斷式

僅節錄片段，詳細可參考
這篇文章 [SQL] 使用 Stored Procedure 動態組成 SQL 查詢指令

CREATE PROCEDURE dbo.QueryProduct
 (
  @ProductID smallint
 )
 AS
 --設定三組sql變數
 DECLARE @statement nvarchar(256)
 DECLARE @orderby nvarchar(40)
 DECLARE @where nvarchar(256)
 
 --給予變數初始值
 SET @statement = 'SELECT [ProductID] FROM Table'
 SET @orderby = ' ORDER BY [ProductID] '

/*------- WHERE 的 if 範例 -------------- */
IF (@ProductID IS NULL)
  BEGIN
   SET @where = ' WHERE 1 = 1 '
  END
 ELSE
  BEGIN
   --如果資料庫的ProductID是int的話就不用在這邊使用CAST轉型了
   SET @where = ' WHERE [ProductID] = ' + CAST(@ProductID AS varchar) 
  
  END

-- 組合字串
SET @statement = @statement + @where + @orderby
 
 PRINT 'STATEMENT: ' + @statement
 EXEC sp_executesql @statement

 RETURN

---

參考來源

執行預存程序
https://docs.microsoft.com/zh-tw/sql/relational-databases/stored-procedures/execute-a-stored-procedure?view=sql-server-ver15

datetime (Transact-SQL)
https://docs.microsoft.com/zh-tw/sql/t-sql/data-types/datetime-transact-sql?view=sql-server-ver15

#TEMPTABLE 、##TEMPTABLE 、@TEMPTABLE 區別?!
https://dotblogs.com.tw/hellouk/2010/10/07/18163

SQL - Temp Table 小技巧
https://dotblogs.com.tw/justforgood/2014/02/20/144091

[iT鐵人賽Day6]SQL Server 資料型態 char varchar nchar nvarchar
https://ithelp.ithome.com.tw/articles/10213922

[SQL] 使用 Stored Procedure 動態組成 SQL 查詢指令
https://dotblogs.com.tw/johnny/2010/01/25/13291

---

tags: 資料庫