melo 唐

1.基本的預存程序

-- PROC:宣告為預存程序

-- usp_showSUpply:預存程序名子

create PROC usp_showSUpply

AS

select *

from 供應商

GO

2.具有輸入參數之預存程序

--輸入參數為@multiple＿

create PROC usp_show_02

@multiple int 
as
select 供應商名稱,類別名稱,產品編號,產品名稱,庫存量,安全存量,
(安全存量*@multiple - 庫存量) as 建議進貨量
from 產品資料 as P, 產品類別 as C, 供應商 as S
where P.類別編號 = C.類別編號 and
P.供應商編號 = S.供應商編號 and
庫存量 < 安全存量
order by S.供應商編號

3.具有輸入參數(有預設值)之預存程序

雖然有預設值,但user還是可以輸入,如果沒有輸入才以預設值為準。

create PROC usp_show_02

@multiple int  = 2
as
select 供應商名稱,類別名稱,產品編號,產品名稱,庫存量,安全存量,
(安全存量*@multiple - 庫存量) as 建議進貨量
from 產品資料 as P, 產品類別 as C, 供應商 as S
where P.類別編號 = C.類別編號 and
P.供應商編號 = S.供應商編號 and
庫存量 < 安全存量
order by S.供應商編號

4.具有輸入與輸出參數之預存程序(用OUTPUT) 

透過output類型之參數,傳回執行結果

CREATE PROC [dbo].[usp_showLowInventory_04]
@multiple int , @total int output
as

select 供應商名稱,類別名稱,產品編號,產品名稱,庫存量,安全存量,

(安全存量*@multiple - 庫存量) as 建議進貨量
from 產品資料 as P, 產品類別 as C, 供應商 as S
where P.類別編號 = C.類別編號 and
P.供應商編號 = S.供應商編號 and
庫存量 < 安全存量
order by S.供應商編號

-- 這邊是給OUTPUT用的

select @total = (安全存量*@multiple - 庫存量)*平均成本

from 產品資料
where 庫存量 < 安全存量

5.具有輸入與輸出參數之預存程序(用RETURN N) 

此方式回傳之N為整數,可以是正或負以及0

此法常拿來判斷sp是否執行成功,例如可以把0:當執行成功,1:執行失敗,或是2...利用不同數字代表不同情況。

此範例利用 0:代表產品資料可以查到資料,若查不到就回傳1,另外設定2:代表產品資料與產品類別都查不到資料。

ALTER PROC [dbo].[usp_queryProd]
--@categoryID:給USER輸入之參數
@categoryID int
AS
--@errCount:用來判斷執行結果
DECLARE @errCount int = 0

select *
from 產品資料
where 類別編號 = @categoryID

--@@ROWCOUNT會根據上面的SELECT,將其影響之行數設定給此參數
--也就是說如果@@ROWCOUNT = 0,代表沒有查到(筆數為0)
if @@ROWCOUNT = 0
begin
--因為沒有查到資料,我們就將@errCount + 1,表示產品資料查不到
set @errCount = @errCount + 1

select *
from 產品類別
where 類別編號 = @categoryID
--@@ROWCOUNT會根據上面的SELECT,將其影響之行數設定給此參數
--也就是說如果@@ROWCOUNT = 0,代表 類別編號沒有查到(筆數為0)
if @@ROWCOUNT = 0
--因為沒有查到資料,我門就將@errCount再 + 1,表示產品類別也查不到
set @errCount = @errCount + 1
end
--回傳@errCount
return @errCount