SQL 书籍推荐(示例SQL 错误处理)

看过很多SQL入门的书籍， 最值得推荐的还是《MICROSOFT+SQL+SERVER+2008技术内幕：T-SQL语言基础》

看完上面那本， 并且在工作中实践了之后， 可以继续看《Microsoft SQL Server 2008技术内幕：T-SQL查询》

和另外一本《Inside_Microsoft_SQL_Server_2008_T-SQL_Programming 》，这本暂时没中文版。

下面是摘抄自本书的关于SQL错误处理的相关内容。

CREATE TABLE dbo.Employees
(
	empid INT NOT NULL,
	empname VARCHAR(25) NOT NULL,
	mgrid INT NULL,
	CONSTRAINT PK_Employees PRIMARY KEY(empid),
	CONSTRAINT CHK_Employees_empid CHECK(empid>0),
	CONSTRAINT FK_Employees
		FOREIGN KEY(mgrid) REFERENCES dbo.Employees(empid)
)


BEGIN TRY

	INSERT INTO dbo.Employees ( empid, empname, mgrid )
	VALUES  ( 1, 'emp1', NULL  );
END TRY
BEGIN CATCH
	IF ERROR_NUMBER()=2627
		PRINT '		Handling PK violation...';
	ELSE IF ERROR_NUMBER()=547
		PRINT '		Handling Check/FK constraint violation...';
	ELSE IF ERROR_NUMBER()=515
		PRINT '		Handling Null violation...';
	ELSE IF ERROR_NUMBER()=245
		PRINT '		Handling conversion error...';
	ELSE
		PRINT '		Handling Unknow error...';

	PRINT	'ERROR Number:' + CAST(ERROR_NUMBER() AS VARCHAR(10));
	PRINT	'ERROR Message:' + ERROR_MESSAGE();
	PRINT	'ERROR SEVERITY:' + CAST (ERROR_SEVERITY() AS VARCHAR(10));
	PRINT	'ERROR State: '+ CAST(ERROR_STATE() AS VARCHAR(10));
	PRINT	'ERROR LINE:'+ CAST (ERROR_LINE() AS VARCHAR(10));
	PRINT	'ERROR PROC:' + COALESCE(ERROR_PROCEDURE(),'Not within proc');

END CATCH;

/*
	当第二次运行上面这段代码时，INSERT 语句将会失败， 流程控制转移到CATCH块，会有以下的错误信息。
	要查看其它错误的效果，可以用0,'A', NULL作为雇员ID来运行这段代码。

(0 行受影响)
		Handling PK violation...
ERROR Number:2627
ERROR Message:违反了 PRIMARY KEY 约束“PK_Employees”。不能在对象“dbo.Employees”中插入重复键。重复键值为 (1)。
ERROR SEVERITY:14
ERROR State: 1
ERROR LINE:5
ERROR PROC:Not within proc

*/
	

IF OBJECT_ID('dbo.ups_use_err_message','P') IS NOT NULL
	DROP PROC dbo.ups_use_err_message;
GO

CREATE PROC dbo.ups_user_err_message
AS

	IF ERROR_NUMBER()=2627
		PRINT '		Handling PK violation...';
	ELSE IF ERROR_NUMBER()=547
		PRINT '		Handling Check/FK constraint violation...';
	ELSE IF ERROR_NUMBER()=515
		PRINT '		Handling Null violation...';
	ELSE IF ERROR_NUMBER()=245
		PRINT '		Handling conversion error...';
	ELSE
		PRINT '		Handling Unknow error...';

	PRINT	'ERROR Number:' + CAST(ERROR_NUMBER() AS VARCHAR(10));
	PRINT	'ERROR Message:' + ERROR_MESSAGE();
	PRINT	'ERROR SEVERITY:' + CAST (ERROR_SEVERITY() AS VARCHAR(10));
	PRINT	'ERROR State: '+ CAST(ERROR_STATE() AS VARCHAR(10));
	PRINT	'ERROR LINE:'+ CAST (ERROR_LINE() AS VARCHAR(10));
	PRINT	'ERROR PROC:' + COALESCE(ERROR_PROCEDURE(),'Not within proc');

GO

/*  
	现在上面的测试就可以直接调用处理错误的存储过程来处理了。
*/

BEGIN TRY

	INSERT INTO dbo.Employees ( empid, empname, mgrid )
	VALUES  ( 1, 'emp1', NULL  );
END TRY
BEGIN CATCH
	EXEC dbo.ups_user_err_message

END CATCH;

/*
　　这样处理， 就可以只在数据库的一个地方对可重用的错误处理代码进行维护。
*/

--DROP TABLE dbo.Employees