SQL SERVER 2005 存储过程_sql server 2005打开存储过程-优快云博客

本文介绍了使用SQL创建存储过程的方法，并演示了如何通过条件判断和错误捕获来增强存储过程的功能。文中还包含了创建表的示例及返回值的使用，展示了在遇到错误时如何进行有效的错误管理和记录。

USE AdventureWorks GO CREATE PROC spEmployee AS SELECT * FROM Humanresources.Employee EXEC spEmployee ALTER PROC spEmployee AS SELECT EmployeeID FROM Humanresources.Employee drop proc spEmployee ALTER PROC spEmployee @LastName nvarchar(50) = NULL AS IF @LastName IS NULL SELECT * FROM HumanResources.Employee ELSE SELECT c.LastName,c.FirstName,e.* FROM Person.Contact c INNER JOIN HumanResources.Employee e ON c.ContactID = e.ContactID WHERE c.LastName LIKE @LastName + '%' EXEC spEmployee BEGIN TRY CREATE TABLE OurIFTest ( COL INT PRIMARY KEY ) END TRY BEGIN CATCH DECLARE @MyOutParameter int IF ERROR_NUMBER() = 2714 BEGIN PRINT 'WARNING SHIPPING CREATE AS TABLE ALREADY EXISTS' EXEC dbo.uspLogError @ErrorLogID = @MyOutParameter OUTPUT PRINT 'A ERROR WAS LOGGED.THE ERROR ID WAS ' + CAST(@MyOutParameter AS VARCHAR) END ELSE RAISERROR('SOMETHINGS NOT GOOD HAPPENED THIS AROUND',16,1) END CATCH DROP TABLE OurIFTest CREATE TABLE OurIFTest ( COL INT PRIMARY KEY ) IF @@ERROR != 0 PRINT 'PROBLEM' ELSE PRINT 'EVERYTHING ID OK !' CREATE PROC spTestRETURN AS DECLARE @MyMessage varchar(50) DECLARE @MyOtherMessage varchar(50) SELECT @MyMessage = 'HI,IT''S THAT LINE BEFOR THE RETURN' PRINT @MyMessage RETURN SELECT @MyOtherMessage = 'SORRY,BUT WE WON''T GET THIS FAR' RETURN @MyOtherMessage RETURN ALTER PROC spTestRETURN AS DECLARE @MyMessage varchar(50) DECLARE @MyOtherMessage varchar(50) SELECT @MyMessage = 'HI,IT''S THAT LINE BEFOR THE RETURN' PRINT @MyMessage RETURN 100 SELECT @MyOtherMessage = 'SORRY,BUT WE WON''T GET THIS FAR' PRINT @MyOtherMessage RETURN DECLARE @ReturnVal int EXEC @ReturnVal = spTestRETURN SELECT @ReturnVal DECLARE @Error int INSERT INTO Sales.Individual (CustomerID,ContactID) VALUES (0,0) SELECT @Error = @@ERROR PRINT '' PRINT 'THE VALUE OF ERROR IS ' + CONVERT(VARCHAR,@Error) PRINT 'THE VALUE OF @@ERROR IS ' + CONVERT(VARCHAR,@@ERROR) ALTER PROC spInsertValidateStoreContact @CustomerID int, @ContactID int, @ContactTypeID int AS BEGIN DECLARE @Error int INSERT INTO Sales.StoreContact (CustomerID,ContactID,ContactTypeID) VALUES (@CustomerID,@ContactID,@ContactTypeID) SELECT @Error = @@ERROR IF @Error = 0 PRINT 'NEW RECORD INSERTED' ELSE BEGIN IF @Error = 547 PRINT 'AT LEAST ONE PROVIDED PARAMETER WAS NOT FUND' ELSE PRINT 'UNKNOWN ERROR' END END EXEC spInsertValidateStoreContact 0,1,11