_ | 覦覈襦 | 豕蠏手 | 殊螳 | 譯殊碁
FrontPage › SQLServer2005T-SQL

Contents

1 Data Type
2 Partitioned Function & Table
3 Try ... Catch


覓語 譴...

1 Data Type #

蠍一ヾ varchar, nvarchar, varbinary 8000覦危語 螳譟. 蠏碁 2005 覯 max れ襯 2GB蟾讌 蟆 . XML 一危 襦 襷れ伎螻, 2GB蟾讌 .

Use AdventureWorks
Go
 
Create Table SalesData.Max_Varchar_Table(
       Max_Varchar Varchar(Max)
,      Max_nVarchar nVarchar(Max)
,      Max_varBinary varBinary(Max)
)
Go
 
Create Table SalesData.Max_XML_Table(
       Max_XML_Col XML)
Go

2 Partitioned Function & Table #


3 Try ... Catch #

伎 覯 語襴螳 譬 蟲碁 蟆 れ企. 企 蟆 覦 Try Catch 蟲覓語 蟆 覯碁. 蟲覓語 蠍 伎 If @@Error 貊螳 譯朱 蠍磯 覃, XACT_ABORT ON 旧 .
BEGIN TRY
     { sql_statement | statement_block }
END TRY
BEGIN CATCH
     { sql_statement | statement_block }
END CATCH
[ ; ]

れ Try Catch 螳 企. 覓碁螻 螳 譴螳 Go 螳 ろ 覓語螳 れ願覃 .
BEGIN TRY
    SELECT *
        FROM sys.messages
        WHERE message_id = 21;
END TRY
-- GO
-- The previous GO breaks the script into two batches,
-- generating syntax errors. The script runs if this GO
-- is removed.
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber;
END CATCH;
GO

れ螻 螳 Error Message襯 訖れ .
USE AdventureWorks;
GO
BEGIN TRANSACTION;
GO
 
BEGIN TRY
    -- Generate a constraint violation error.
    DELETE FROM Production.Product
        WHERE ProductID = 980;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() as ErrorState,
        ERROR_MESSAGE() as ErrorMessage;
END CATCH
GO
 
ROLLBACK TRANSACTION;
GO

Try Catch襯 伎 るジ 2螳讌 覦覯 螳襦 蟆. 豌 覯讌 覦覯 @@Error襯 伎 覦覯企. Insert 覓語螳 る Commit 螻, 覓語螳 覦覃 Catch襦 企 豬螻 . XACT_ABORT ON螻 @@Error螳 . @@Error 旧企, XACT_ABORT 覦 .

Use Tempdb
Go
 
CREATE TABLE dbo.DataTable 
       (ColA int PRIMARY KEY, ColB int)
CREATE TABLE dbo.ErrorLog 
       (ColA int, ColB int, error int, date datetime)
GO
 
CREATE PROCEDURE dbo.AddData @a int, @b int 
AS
SET XACT_ABORT ON
BEGIN TRY
       BEGIN TRAN
             INSERT INTO dbo.DataTable VALUES (@a, @b)
       COMMIT TRAN
END TRY
 
BEGIN CATCH   
       DECLARE @err int
       SET @err = @@error --trap the error number
       ROLLBACK TRAN
       INSERT INTO dbo.ErrorLog VALUES (@a, @b, @err, GETDATE())
END CATCH
GO
 
EXEC dbo.AddData 1, 1
EXEC dbo.AddData 2, 2
EXEC dbo.AddData 1, 3 --violates the primary key
 
Select * From dbo.ErrorLog 
Go
/*
ColA          ColB         error         date
----------- ----------- ----------- -----------------------
1             3              2627          2005-02-12 13:54:54.377
 
(1 row(s) affected)
*/

覯讌 蟆 覲 蟆 XACT_STATE()襯 伎 蟆企. -1 企 碁 企 伎語 覈襯伎襷 ろ 蟆 企, 1企 炎概企. 0企 讌 譴企. 企 蟆 伎 XACT_ABORT ON螻 COMMIT TRAN 伎 譯殊豌襴 るジ 覦覯 . 覓伎^蟇 Error螳 覦 蟆豌 螳ロ伎 覩襦 RAISERROR襯 蟆 覲 . RAISERROR 覦 .
Use Tempdb
Go
 
CREATE TABLE dbo.DataTable 
       (ColA int PRIMARY KEY, ColB int)
CREATE TABLE dbo.ErrorLog 
       (ColA int, ColB int, error int, date datetime)
GO
 
CREATE PROCEDURE dbo.AddData @a int, @b int 
AS
--SET XACT_ABORT ON
BEGIN TRY
       BEGIN TRANSACTION;
             INSERT INTO dbo.DataTable VALUES (@a, @b)
       RAISERROR(N'Throw an error.', 16, 1); -- Need
       --COMMIT TRAN
END TRY
 
BEGIN CATCH   
DECLARE @err int
IF (XACT_STATE()) = -1
        ROLLBACK TRANSACTION;
             SET @err = @@error --trap the error number
             INSERT INTO dbo.ErrorLog VALUES (@a, @b, @err, GETDATE())
    IF (XACT_STATE()) = 1
        COMMIT TRANSACTION;
END CATCH
GO
 
EXEC dbo.AddData 1, 1
EXEC dbo.AddData 2, 2
EXEC dbo.AddData 1, 3 --violates the primary key
 
Select * From dbo.ErrorLog 
Go
蠍 蠍郁鍵..
企: : るジ讓曙 襦螻豺 企Ν 譯殊語. 襦螻豺
EditText : Print : Mobile : FindPage : DeletePage : LikePages : Powered by MoniWiki : Last modified 2018-04-13 23:12:54

覓語 豕 蠍 襷 轟企. (豌豺)