# L12 預存程序
###### tags: `SQL`
預存程序 Stored Precedure
實際上為一個小程式
建議擁有者設定為dbo
系統預設預存程序常見縮寫為 **sp_**
使用者建立的預存程序建議可設為 usp_
執行預存程序時： ==**EXEC** 預存程序名稱==
**syscomments**為系統檢視表，可以看到系統預存程序，
but如果檢視表已加密，系統檢視表syscomments中檢視表定義也會加密
```sql=
SELECT* FROM syscomments
GO
```
分幾種型態：
* System
在**Master**資料庫，常見縮寫為sp
可以按 預存程序名稱 右鍵-> 修改-> 看到程式碼
* Local
使用者建立的
* Exended
擴充的，一樣存在Master資料庫，但不能看到程式碼(因為是.dll檔)
預存程序優點：
* 可被共用
* 可隱藏資料庫細節
* 簡化權限設定-> **安全性**考量
* 改善效能
* 降低網路流量
## 基本語法
針對物件：
新增 **CREATE**
修改 **ALTER**
刪除 **DROP**
## 新增預存程序
:::info
**CREATE PROC** 預存程序
AS
定義(Select...From...Where...)
:::
ex.
```sql=
USE Northwind
GO
CREATE PROC dbo.OverdueOrders
AS
SELECT *
FROM dbo.Orders
WHERE RequiredDate < GETDATE() AND ShippedDate IS Null
GO
```
## 預存程序執行的結果(資料)傳回資料表中
::: info
**INSERT** 資料表
**EXEC** 預存程序
:::
ex.將預存程序執行的結果(資料)傳回資料表中
```sql=
USE Northwind
GO
CREATE PROC dbo.EmployeeCustomer
AS
SELECT
UPPER(SUBSTRING(LastName, 1, 4)+SUBSTRING(FirstName, 1,1)),
'Northwind Traders', RTRIM(FirstName)+' '+LastName,
'Employee', Address, City, Region, PostalCode, Country,
('(206) 555-1234'+' x'+Extension), NULL
FROM Employees
WHERE HireDate < GETDATE()
GO
INSERT [Customers]
EXEC EmployeeCustomer
GO
```
## 修改預存程序
:::info
**ALTER PROC** 預存程序
AS
定義(Select...From...Where...)
:::
ex.
```sql=
USE Northwind
GO
ALTER PROC dbo.OverdueOrders
AS
SELECT CONVERT(char(8), RequiredDate, 1) RequiredDate,
CONVERT(char(8), OrderDate, 1) OrderDate,
OrderID, CustomerID, EmployeeID
FROM Orders
WHERE RequiredDate < GETDATE() AND ShippedDate IS Null
ORDER BY RequiredDate
GO
```
## 刪除預存程序
:::info
**DROP** 預存程序
:::
ex.
```sql=
DROP PROC OverdueOrders
```
## 預存程序帶入參數
ex.
```sql=
USE Northwind
GO
CREATE PROC [Year to Year Sales]
@BeginningDate DateTime, @EndingDate DateTime
AS
SELECT ShippedDate, O.OrderID,
Subtotal, Year=DATENAME(yy,ShippedDate)
FROM Orders O
INNER JOIN [Order Subtotals] OS
ON O.OrderID = OS.OrderID
WHERE
ShippedDate Between @BeginningDate And @EndingDate
GO
--執行
EXEC [Year to Year Sales] '1997/7/1','1997/7/31'
GO
```
## 傳回執行結果
使用 **OUTPUT** 參數
執行時，*先宣告*Declare
ex.
```sql=
USE Northwind
GO
CREATE PROCEDURE dbo.MathTutor
@m1 smallint, @m2 smallint, @result smallint OUTPUT
AS
SET @result = @m1* @m2
GO
--執行
DECLARE @answer smallint
EXEC MathTutor 5,6, @answer OUTPUT
SELECT 'The result is:', @answer
GO
```
## RaisError
==**RaisError**('回傳訊息',嚴重程度,程式位置)==
嚴重程度10以下表非錯誤只是訊息
```sql=
RAISERROR (@Message, 10 ,1)
```
Sign in with Wallet
Connect another wallet