Microsoft SQL Server/儲存過程

介紹

一個儲存過程 (sp) 是一組 SQL 請求，儲存在資料庫中。在 SSMS 中，它們可以與表格一起找到。

實際上，在軟體架構方面，最好將 T-SQL 語言儲存在資料庫中，因為如果一個層發生變化，則不需要修改另一個層。

通常儲存過程操作其資料庫表，但它們也可以與其他資料庫表進行互動，即使這些表位於另一個伺服器（稱為連結伺服器）上。要建立連結伺服器

• 在 SSMS 中，單擊“伺服器物件”選單中的“連結伺服器”，然後填寫用於連線的帳戶。
• 在 SQL 中，使用 sp_addlinkedserver。

跨兩個伺服器聯接的示例

select *
from table1 t1
inner join [server2].[base2].[dbo].[table2] t2 on t2.id = t1.t2_id

語法

Microsoft T-SQL 語言提供了一些從 SQL 標準改進。

• 預設情況下，引號的作用不同於撇號，撇號用於建立字元字串。要在同一方式下使用它們（例如巢狀它們），應該啟動 SET QUOTED_IDENTIFIER ON。
• 在 SSMS 中，SQL 請求可以透過三種方式執行

1. 直接在空白視窗中，點選“新建查詢”即可看到。然後可以將其儲存為 .sql 檔案，以便能夠在同一個視窗中重新開啟它。
2. 透過將其儲存在字串變數中，然後使用 sp_executesql 執行它。這樣做的好處是可以包含變數（例如：資料庫名稱），但缺點是抑制了語法著色、自動完成（IntelliSense）和 SSMS 除錯。例如

    DECLARE @Request1 NVARCHAR(MAX)
    DECLARE @MyTable1 NVARCHAR(MAX)
    SET @MyTable1 = 
    SET @Requst1 =  'SELECT * FROM ' + @MyTable1
    EXECUTE sp_executesql @Request1
   

3. 透過執行儲存在資料庫中的過程，該過程包含請求。例如

    EXEC [MaBase1].[dbo].[MyProcedure1]
   

此呼叫可以後跟引數，類似於指令式程式設計中的過程。

實際上，儲存過程中有兩種型別的變數

1. 私有變數，以 Declare 開頭。
2. 引數

@StartDate varchar(8)       -- Mandatory argument
@EndDate varchar(8) = null  -- Optional argument
if @EndDate is null set @EndDate = convert(varchar,@StartDate + 1,112)
Declare @Name varchar(50)   -- Private variable

要建立新的儲存過程

CREATE PROCEDURE [dbo].[MyProcedure1]

要儲存現有儲存過程

ALTER PROCEDURE [dbo].[MyProcedure1]

理想情況下，此指令應出現在 sp 的開頭，後跟 AS + 它的名稱，這樣程式碼執行就會儲存它（而不是啟動它）。為了獲得其結果，SSMS 提供了右鍵單擊選項：“執行儲存過程...”這將生成另一個 SQL 請求，該請求在結果上方開啟一個新標籤頁，並使用其引數呼叫儲存過程。

注意：SSMS 不允許備份包含編譯錯誤的儲存過程。因此，如果備份緊急，只需註釋掉錯誤程式碼或建立臨時 .sql 檔案。

注意：錯誤訊息會傳達一個行號，該行號與 SSMS 行不匹配。它實際上是從最後一個 GO 偏移的。

然後，這些 sp 可以被任何提供 SQL Server 驅動程式的程式語言中的程式呼叫，例如 PHP 或 VB，並將從 recordset 變數中呈現結果。

PRINT

此命令在 訊息 選項卡中顯示內容，與填充 結果 選項卡的 SELECT 相反。

示例

print 'Hello World ! ' -- Displays "Hello World !"

declare @n int
set @n = 5

print 'the value is: ' + cast(@n as varchar)

條件

IF

if @x=1 begin
  print 'x = 1'
end else if @x=2 begin
  print 'x = 2'
end else begin
  print 'x <> 1 et 2'
end

備註：begin 和 end 是可選的。

CASE

set @Season = case 
 when @DayDate = '20110918' then 'summer'
 when @DayDate = '20110922' then 'autumn'
 else 'another season'
end

要僅在存在值時新增 WHERE 條件，技巧是在其他情況下設定始終為真的內容（例如：Field1 = Field1）

declare @Column int = null
select Field1
from Table1
where Field1 = case when isnull(@Column,'')<>'' then @Column else Field1 end

上面的示例可以使用 where Field1 = isnull(@Column, Field1) 更簡單。

迴圈

WHILE

“while” 迴圈使用條件來停止，例如計數器

DECLARE @i int
WHILE @i <= 10
BEGIN
   UPDATE Table1
   SET Field2 = "petit" WHERE Field1 = @i
   SET @i = @i + 1
END

CURSOR

遊標允許逐行處理記錄集，每行都儲存在 INTO 後面提到的變數中，並在 NEXT 之後重新初始化。但是，這種方法相對較慢，應該避免使用，只要有可能。

例如，如果一個記錄處理依賴於前一個記錄，或者要列印一些字元

USE Base1
declare @Name varchar(20)
DECLARE cursor1 CURSOR FOR SELECT FirstName FROM Table1
OPEN cursor1

/* First record from the selection */
FETCH NEXT FROM cursor1 into @Name
print 'Hello ' + @Name

/* Treatment of the other records in a loop */
while @@FETCH_STATUS = 0
  begin
    FETCH NEXT FROM cursor1 into @Name
    print 'Hello ' + @Name
  end

CLOSE cursor1;
DEALLOCATE cursor1;

從另一個儲存過程執行儲存過程

SSMS 還提供了一個逐步執行模式（類似於 Visual Basic），透過在每一步按 F11，可以跟蹤左下角的變數值。

斷點也可用，用於從一行跳轉到另一行。

備註：在超程式設計中，任何 sp 修改都不會被過程在執行期間考慮在內。

要從另一個 sp 執行 sp

ALTER PROCEDURE [dbo].[MyProcedure1]
DECLARE	@result int
EXEC	@result = [dbo].[MyProcedure2] @Parameter1;
if @result = 0 begin
 ...
end

異常

從 SQL Server 2005 開始，異常處理看起來像這樣

-- Transaction start
BEGIN TRAN
  BEGIN TRY
   -- Execution
   INSERT INTO Table1(Name1) VALUES ('ABC')
   INSERT INTO Table1(Name1) VALUES ('123')
   -- Transaction submission
   COMMIT TRAN
  END TRY
BEGIN CATCH
 -- Transaction cancellation if error
 ROLLBACK TRAN
END CATCH

研究

要獲取包含特定字串的 sp

SELECT name
FROM sysobjects syso
INNER JOIN syscomments sysc
ON syso.id = sysc.id
WHERE
(syso.xtype = 'P' or
syso.xtype = 'V')
AND
(syso.category = 0)
and text like '%String to search%'
group by name