EXEC sp_who
GO

EXEC sp_who 'sa'
GO

EXEC sp_who 5
GO

EXEC sp_who 'active'
GO

EXEC sp_attach_db 'CH13範例資料庫',
                  'C:\Databases\CH13範例資料庫.mdf', 
                  'C:\Databases\CH13範例資料庫_log.ldf',
                  'C:\Databases\CH13範例資料庫G11.ndf',
                  'C:\Databases\CH13範例資料庫G21.ndf',
                  'C:\Databases\CH13範例資料庫G22.ndf'
GO

EXEC sp_detach_db 'CH13範例資料庫'
GO

EXEC sp_renamedb 'CH13範例資料庫' , 'CH13新名稱資料庫'
GO

EXEC sp_renamedb 'CH13新名稱資料庫' , 'CH13範例資料庫'
GO

EXEC sp_helpdb
GO

EXEC sp_helpdb 'CH13範例資料庫'
GO

USE CH13範例資料庫
EXEC sp_helpfilegroup
GO
 
USE CH13範例資料庫
EXEC sp_helpfilegroup 'CH13G2'
GO

USE CH13範例資料庫
EXEC sp_helpfile
GO

USE CH13範例資料庫
EXEC sp_helpfile 'CH13範例資料庫G22'
GO

USE CH13範例資料庫
EXEC sp_rename '供應商' , '廠商資料'
GO

USE CH13範例資料庫
EXEC sp_rename '廠商資料.供應商名稱' , '廠商名稱' , 'COLUMN'
GO

EXEC sp_rename '廠商資料' , '供應商'
EXEC sp_rename '供應商.廠商名稱' , '供應商名稱', 'COLUMN'
GO

USE CH13範例資料庫
EXEC sp_helpconstraint '訂單'
GO

CREATE PROC usp_showSupply
AS
SELECT *
FROM 供應商
GO

EXEC usp_showSupply

CREATE PROC usp_showLowInventory
AS
SELECT 供應商名稱, 類別名稱, 產品編號, 產品名稱, 庫存量, 安全存量, 
(安全存量*2 - 庫存量) AS 建議進貨量
FROM 產品資料 as P , 產品類別 as C, 供應商 as S
WHERE P.類別編號 = C.類別編號 AND
       P.供應商編號 = S.供應商編號 AND
       庫存量 < 安全存量
ORDER BY S.供應商編號, C.類別編號, 產品編號
GO

EXEC usp_showLowInventory

CREATE PROC usp_showLowInventory_02
@multiple int
AS
PRINT '使用' + CAST(@multiple as varchar) + '倍的安全存量計算'
SELECT 供應商名稱, 類別名稱, 產品編號, 產品名稱, 庫存量, 安全存量, 
(安全存量*@multiple - 庫存量) AS 建議進貨量
FROM 產品資料 AS P , 產品類別 AS C, 供應商 AS S
WHERE P.類別編號 = C.類別編號 AND
        P.供應商編號 = S.供應商編號 AND
        庫存量 < 安全存量
ORDER BY S.供應商編號, C.類別編號, 產品編號
GO

EXEC usp_showLowInventory_02 3

EXEC usp_showLowInventory_02

CREATE PROC usp_showLowInventory_03
@multiple int = 2
AS
PRINT '使用' + CAST(@multiple as varchar) + '倍的安全存量計算'
SELECT 供應商名稱, 類別名稱, 產品編號, 產品名稱, 庫存量, 安全存量, 
(安全存量*@multiple - 庫存量) AS 建議進貨量
FROM 產品資料 AS P , 產品類別 AS C, 供應商 AS S
WHERE P.類別編號 = C.類別編號 AND
       P.供應商編號 = S.供應商編號 AND
       庫存量 < 安全存量
ORDER BY S.供應商編號, C.類別編號, 產品編號
GO

EXEC usp_showLowInventory_03

EXEC usp_showLowInventory_03 3

CREATE PROC usp_showLowInventory_04
@multiple int = 2, @total int OUTPUT
AS
SELECT 供應商名稱, 類別名稱, 產品編號, 產品名稱, 庫存量, 安全存量, 
       (安全存量*@multiple - 庫存量) AS 建議進貨量
FROM 產品資料 AS P , 產品類別 AS C, 供應商 AS S
WHERE P.類別編號 = C.類別編號 AND
       P.供應商編號 = S.供應商編號 AND
       庫存量 < 安全存量
ORDER BY S.供應商編號, C.類別編號, 產品編號

SELECT @total = (安全存量*@multiple - 庫存量)*平均成本
FROM 產品資料
WHERE 庫存量 < 安全存量
GO

DECLARE @OrderTotal int
EXEC usp_showLowInventory_04 3, @OrderTotal OUTPUT
Print '預計訂貨總金額：' + CAST(@OrderTotal AS varchar) + ' 元'
GO

DECLARE @OrderTotal int
EXEC usp_showLowInventory_04 default, @OrderTotal OUTPUT
PRINT '預計訂貨總金額：' + CAST(@OrderTotal AS varchar) + ' 元'
GO

CREATE PROC usp_listPrizeEmp
@lowBound int = 0
WITH  RECOMPILE
AS
SELECT E.員工編號, 姓名, SUM(實際單價*數量) 營業額
FROM  員工 E, 訂單 O, 訂單明細 OD
WHERE E.員工編號 = O.員工編號 AND
       O.訂單編號 = OD.訂單編號
GROUP BY  E.員工編號, 姓名
HAVING SUM(實際單價*數量) > @lowBound
ORDER BY SUM(實際單價*數量) DESC
GO

EXEC usp_listPrizeEmp 2000

CREATE PROC usp_calcPrize
@lowBound int = 0,
@unitPrize int = 1000
WITH  ENCRYPTION
AS
SELECT E.員工編號, 姓名, SUM(實際單價*數量) 營業額, 
       round(((SUM(實際單價*數量) - @lowBound) / 1000), 0)*@unitPrize 獎金
FROM  員工 E, 訂單 O, 訂單明細 OD
WHERE E.員工編號 = O.員工編號 AND
		O.訂單編號 = OD.訂單編號
GROUP BY  E.員工編號, 姓名
HAVING SUM(實際單價*數量) > @lowBound
ORDER BY SUM(實際單價*數量) DESC
GO

EXEC usp_calcPrize 2000, 3000

CREATE PROC usp_calcPrize_02
@lowBound int = 0,
@unitPrize int = 1000
WITH  RECOMPILE, ENCRYPTION
AS
SELECT E.員工編號, 姓名, SUM(實際單價*數量) 營業額, 
	round(((SUM(實際單價*數量) - @lowBound)/1000), 0)*@unitPrize 獎金
FROM  員工 E, 訂單 O, 訂單明細 OD
WHERE E.員工編號 = O.員工編號 AND
        O.訂單編號 = OD.訂單編號
GROUP BY  E.員工編號, 姓名
HAVING SUM(實際單價*數量) > @lowBound
ORDER BY SUM(實際單價*數量) DESC
GO

CREATE PROC usp_calcABC
@A int = 5 ,
@B int = NULL ,
@C int
AS
SELECT @A as '@A的值', @B as '@B的值', @C as '@C的值'
GO

EXEC usp_calcABC @A=1, @B=2, @C=3  

EXEC usp_calcABC @B=2, @C=3, @A=1

EXEC usp_calcABC 1, 2, 3

EXEC usp_calcABC @C=5


EXEC usp_calcABC , , 3

EXEC usp_calcABC DEFAULT, DEFAULT, 3


EXEC usp_calcABC @A=1, 2, @C=3


EXEC usp_calcABC DEFAULT, DEFAULT, @C=3

EXEC usp_calcABC @A=DEFAULT, @B=DEFAULT, @C=3

CREATE PROC usp_queryProd
@CategoryID int
AS
DECLARE @errCount int = 0 
SET NOCOUNT ON 
SELECT * 
FROM 產品資料
WHERE 類別編號 = @CategoryID

IF @@ROWCOUNT = 0
BEGIN
SET @errCount = @errCount + 1

SELECT *
FROM 產品類別
WHERE 類別編號 = @CategoryID

IF @@ROWCOUNT = 0
      SET @errCount = @errCount + 1
END

RETURN @errCount
GO

DECLARE @errCode int
EXEC @errCode = usp_queryProd 7
IF @errCode = 0
   PRINT '查詢成功'
ELSE IF @errCode = 1
   PRINT '[產品資料]資料表中沒有此類產品'
ELSE
   PRINT '[產品資料]與[產品類別]資料表，皆無此類別產品'
GO  

CREATE PROC queryTable
@tableName varchar(100)
AS
DECLARE @sqlStr varchar(100)
SET @sqlStr = 'SELECT * FROM ' + @tableName
PRINT '@sqlStr = ' + @sqlStr    -- 可利用@sqlStr的輸出來看語法是否錯誤
EXEC (@sqlStr)
GO

EXEC queryTable '客戶'

CREATE PROC usp_queryBoss
@level int = 1
AS
DECLARE @cnter int = 1
DECLARE @sqlstr varchar(500)= ''     --這是兩個單引號
DECLARE @select varchar(500) , 
        @from varchar(500) , 
          @where varchar(500)

SET @select='select E0.員工編號, E0.姓名'
SET @from='from 員工 as E0 '
SET @where=''

WHILE (@cnter <= @level)
BEGIN
     SET @select = @select + ', E' + cast(@cnter as varchar) +'.員工編號 第' + 
                             cast(@cnter as varchar) + '級主管編號' +
                          ', E' + cast(@cnter as varchar) +'.姓名 第' +
                             cast(@cnter as varchar)+ '級主管姓名'  
     SET @from = @from + ', 員工as E' + cast(@cnter as varchar) + ' '        
     IF @cnter = 1
        BEGIN
           SET @where = ' where ' + 'E' + cast(@cnter - 1 as varchar) +'.主管= E'+
                                     cast(@cnter as varchar) +'.員工編號'
        END
     ELSE  
        BEGIN
           SET @where = @where + ' and E' + cast(@cnter - 1 as varchar) +'.主管= E' +
                                       cast(@cnter as varchar) +'.員工編號'                
        END
     SET @cnter = @cnter + 1
END  -- while end
SET @sqlstr=@select + @from + @where
PRINT @sqlstr  -- 此行的輸出可用來SQL語法的偵錯
EXEC (@sqlstr)
GO

EXEC usp_queryBoss 3

CREATE PROC usp_procA
AS
PRINT 'usp_procA 的開始.....@@NESTLEVEL = ' + CAST(@@NESTLEVEL AS varchar)
EXEC usp_procB
PRINT 'usp_procA 的結束.....@@NESTLEVEL = ' + CAST(@@NESTLEVEL AS varchar)
GO

CREATE PROC usp_procB
AS
PRINT '  usp_procB 的開始...@@NESTLEVEL = ' + CAST(@@NESTLEVEL AS varchar)
EXEC usp_procC
PRINT '  usp_procB 的結束...@@NESTLEVEL = ' + CAST(@@NESTLEVEL AS varchar)
GO

CREATE PROC usp_procC
AS
PRINT ' usp_procC........@@NESTLEVEL = ' + CAST(@@NESTLEVEL AS varchar)
GO

EXEC usp_procA

CREATE TABLE [產品資料log]
(
	異動類型 nchar(10) ,
	異動日期 datetime ,
	before產品編號 int ,
	before類別編號 int ,
	before供應商編號 varchar(5) ,
	before產品名稱 varchar(40) ,
	before建議單價 int ,
	before平均成本 int ,
	before庫存量 int ,
	before安全存量 int ,
	after產品編號 int ,
	after類別編號 int ,
	after供應商編號 varchar(5) ,
	after產品名稱 varchar(40) ,
	after建議單價 int ,
	after平均成本 int ,
	after庫存量 int ,
	after安全存量 int 	
) 
GO

CREATE PROC usp_insertOutput
AS
INSERT 產品資料
OUTPUT  'INSERT' as 異動型別, GETDATE() as 異動日期, inserted.* 
VALUES (13, 1,'S0002', '柳丁汁', 20, 15,0,100)
GO

EXEC usp_insertOutput

CREATE PROC usp_insertOutputLog
AS
INSERT 產品資料
OUTPUT 'INSERT', GETDATE(), null , null , null , null , null , null , null , null , inserted.* INTO [產品資料log]
VALUES (14, 1,'S0002', '奇異果汁', 22, 16,0,100), 
        (15, 1,'S0002', '芭樂汁', 20, 13,0,80)
GO

EXEC usp_insertOutputLog

CREATE PROC usp_updateOutput
AS
UPDATE 產品資料
SET 庫存量 = 250
OUTPUT 'UPDATE' as 異動類型, GETDATE() as 異動日期, deleted.產品編號, deleted.產品名稱, deleted.庫存量 as 舊庫存量, inserted.庫存量 as 新庫存量
WHERE 產品編號 >= 13
GO

EXEC usp_updateOutput

CREATE PROC usp_updateOutputLog
AS
UPDATE 產品資料
SET 庫存量 = 300
OUTPUT 'UPDATE', GETDATE(), deleted.*, inserted.* INTO [產品資料log]
WHERE 產品編號 >= 13
GO

EXEC usp_updateOutputLog

CREATE PROC usp_deleteOutput
AS
DELETE 產品資料
OUTPUT 'DELETE' as 異動類型, GETDATE() as 異動日期, deleted.*
WHERE 產品編號 IN (13,14)
GO

EXEC usp_deleteOutput

CREATE PROC usp_deleteOutputLog
AS
DELETE 產品資料
OUTPUT 'DELETE', GETDATE(), deleted.*, null , null , null , null , null , null , null , null INTO [產品資料log]
WHERE 產品編號 = 15
GO

EXEC usp_deleteOutputLog

CREATE PROC usp_mergeOutput
AS
MERGE M目標_產品資料 AS T
USING M來源_產品資料 AS S
ON T.產品編號 = S.產品編號
WHEN MATCHED THEN
     UPDATE 
     SET T.產品名稱=S.產品名稱, T.單價=S.單價
WHEN NOT MATCHED BY TARGET THEN 
     INSERT (產品編號, 產品名稱, 單價, 庫存量)
     VALUES (S.產品編號, S.產品名稱, S.單價, 0)
WHEN NOT MATCHED BY SOURCE THEN 
     DELETE
OUTPUT $action, deleted.*, inserted.*     
;   -- 別忘了在結尾一定要加上『; 』否則會發生語法錯誤
GO

EXEC usp_mergeOutput

ALTER PROC usp_calcPrize
@lowBound int = 0,
@unitPrize int = 1000
AS
SELECT E.員工編號, 姓名, SUM(實際單價*數量) 營業額, 
       round(((SUM(實際單價*數量) - @lowBound) / 1000), 0)*@unitPrize 獎金
FROM  員工 E, 訂單 O, 訂單明細 OD
WHERE E.員工編號 = O.員工編號 AND
		O.訂單編號 = OD.訂單編號
GROUP BY  E.員工編號, 姓名
HAVING SUM(實際單價*數量) > @lowBound
ORDER BY SUM(實際單價*數量) DESC
GO

DROP PROC usp_showSupply
DROP PROC usp_queryBoss
DROP PROC usp_queryProd

DROP PROC usp_insertOutput, usp_updateOutput, usp_listPrizeEmp