HackMD
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 左側並且排除中間重疊部分的語法，見下圖
- The image was uploaded to a note which you don't have access to
- The note which the image was originally uploaded to has been deleted
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
Learn More →
暫存資料表 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
參考來源
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
