DUCK，Fearless！

USE [DuckTest]
GO
/****** Object: StoredProcedure [dbo].[sp_CopyScores_Ver] Script Date: 01/09/2013 11:22:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_CopyScores_Ver]
-- Add the parameters for the stored procedure here
@TestYear int,
@TxVer nvarchar(15) output--設定 Output Parameter 以參數方式傳回
AS

DECLARE @Name nvarchar(50)
DECLARE @Chinese int
DECLARE @English int
DECLARE @Mathematics numeric(18, 2)
DECLARE @Average int
DECLARE @Total int
DECLARE @Sort int
DECLARE @Count int

DECLARE @TxDate nvarchar(8)
DECLARE @TxTime nvarchar(8)

DECLARE @TEMPCOUNT int
BEGIN
BEGIN TRY
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
/*設定基本參數（時間等）*/
SET @TxDate =convert(nvarchar(8),getdate(),112)
SET @TxTime=convert(nvarchar(8),getdate(),114)
SET @TxVer = convert(nvarchar(8),getdate(),112)

SELECT @TEMPCOUNT=COUNT(*) FROM Scores WHERE TestYear=@TestYear
IF @TEMPCOUNT = 0
BEGIN
RETURN;
END
ELSE IF @TEMPCOUNT>0
BEGIN
--更新資料多列時，需要多「CURSOR」來處理
DECLARE InsertDeclare CURSOR
FOR (SELECT Name, Chinese, English, Mathematics FROM Scores WHERE TestYear=@TestYear )--宣告CURSOR名字及資料
OPEN InsertDeclare
BEGIN
FETCH NEXT FROM InsertDeclare INTO @Name, @Chinese, @English, @Mathematics --將第一筆資料填入變數
WHILE @@FETCH_STATUS = 0 /*判斷讀入是否正确*/
BEGIN
IF(@Chinese!=null)
SET @Count = 1;
IF(@English!=null)
SET @Count = 2;
IF(@Mathematics!=null)
SET @Count = 3;

SET @Total= ISNULL(@Chinese, 0)+ISNULL(@English, 0)+ISNULL(@Mathematics, 0);
SET @Average = @Total/@Count;
INSERT INTO Scores_Ver(Name, TestYear, Chinese, English, Mathematics, Average, Total, TxVer, TxDate, TxTime)
VALUES (@Name, @TestYear, @Chinese, @English, @Mathematics, @Average, @Total, @TxVer, @TxDate, @TxTime)

FETCH NEXT FROM InsertDeclare INTO @Name, @Chinese, @English, @Mathematics
END
END
CLOSE InsertDeclare
DEALLOCATE InsertDeclare /*關閉游標，操作结束*/

/***** 如果是新增資料 可以用下面這個 可以回傳新增了幾行 *****/
--使用 RETURN 傳回單一數值
--RETURN SCOPE_IDENTITY();
END
END TRY
BEGIN CATCH
/***** 錯誤訊息宣告 *****/
DECLARE @ErrorNumber int --傳回系統錯誤代號
DECLARE @ErrorSeverity int --傳回錯誤的嚴重程度
DECLARE @ErrorState int --傳回錯誤分類
DECLARE @ErrorProcedure int --傳回錯誤Procedure Name
DECLARE @ErrorLine int --傳回發生錯誤的行數
DECLARE @ErrorMessage NVARCHAR(MAX);--傳回系統錯誤訊息

SELECT
@ErrorNumber = ERROR_NUMBER(),
@ErrorSeverity = ERROR_SEVERITY() ,
@ErrorState = ERROR_STATE(),
@ErrorProcedure = ERROR_PROCEDURE() ,
@ErrorLine = ERROR_LINE() ,
@ErrorMessage = ERROR_MESSAGE();
/***** 將錯誤訊息寫到[sys].[messages] *****/
RAISERROR (5005,
@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
--/***** 查詢錯誤訊息 *****/
--SELECT [message_id]
-- ,[language_id]
-- ,[severity]
-- ,[is_event_logged]
-- ,[text]
--FROM [DuckTest].[sys].[messages]
END CATCH
/***** 因截取到錯誤, 故須回覆先前已異動過的記錄 *****/
--@@TRANCOUNT返回在當前連接上已發生的BEGIN TRANSACTION語句的數量。
--因截取到錯誤, 故須回覆先前已異動過的記錄
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
----將異動的記錄寫回資料庫中
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
END