创建基本储存过程:
CREATE PROC spEmployee
AS
SELECT * FROM HumanResources.Employee;
GO
使用ALTER修改储存过程
使用ALTERPROC和CREATE PROC语句的唯一区别:
ALTER PROC:期望找到一个已有的储存过程,而CREARE则不是。
ALTER PROC:保留了储存过程上已经建立的任何权限。
ALTER PROC:在可能调用被修改的储存过程的其他对象上保留了人和依赖信息。
删除储存过程:
DROP PROC |PROCEDURE<sproc name>[;]
参数化储存过程:
声明参数:
名称。
数据类型。
默认值。
方向。
注:对于名称,有一组简单的规则。首先,他必须以@符号开始。声明数据类型时需要记住的一点是,当声明CURSOR类型参数时,必须也使用VARYING和OUTPUT选项。OUTPUT可简写为OUT。
CREATE PROC spEmployeeByName
@LastName nvarchar(50)
AS
SELECT p.LastName, p.FirstName, e.JobTitle, e.HireDate
FROM Person.Person p
JOIN HumanResources.Employee e
ON p. BusinessEntityID = e.BusinessEntityID
WHERE p.LastName LIKE @LastName + '%';
GO
EXEC spEmployeeByName 'Dobney';
1、提供默认值
CREATE PROC spEmployeeByName
@LastName nvarchar(50) = NULL
AS
IF @LastName IS NOT NULL
SELECT p.LastName, p.FirstName, e.JobTitle, e.HireDate
FROM Person.Person p
JOIN HumanResources.Employee e
ON p.BusinessEntityID = e.BusinessEntityID
WHERE p.LastName LIKE @LastName + '%';
ELSE
SELECT p.LastName, p.FirstName, e.JobTitle, e.HireDate
FROM Person.Person p
JOIN HumanResources.Employee e
ON p.BusinessEntityID = e.BusinessEntityID;
GO
EXEC spEmployeeByName;
GO
2、创建输出参数
BEGIN TRY
-- Try and create our table
CREATE TABLE OurIFTest(
Col1 int PRIMARY KEY
)
END TRY
BEGIN CATCH
-- Uh oh, something went wrong, see if it's something
-- we know what to do with
DECLARE @MyOutputParameter int;
IF ERROR_NUMBER() = 2714 -- Object exists error, we knew this might happen
BEGIN
PRINT 'WARNING: Skipping CREATE as table already exists';
EXEC dbo.uspLogError @ErrorLogID = @MyOutputParameter OUTPUT;
PRINT 'An error was logged. The Log ID for our error was '
+ CAST(@MyOutputParameter AS varchar);
END
ELSE -- hmm, we don't recognize it, so report it and bail
RAISERROR('something not good happened this time around', 16, 1 );
END CATCH
GO
SELECT *
FROM ErrorLog
WHERE ErrorLogID = 1; -- change this value to whatever your
-- results said it was logged as
GO
注:对于储存过程声明中的输出参数,需要使用OUTPUT的关键字。和声明储存过程一样,调用储存过程时必须使用OUTPUT关键字。赋给输出结果的变量不需要和储存过程中的内部参数拥有相同的名称。EXEC关键字是必须的,因为对储存过程的调用并不是批处理要做的第一件事。
如何使用RETURN语句:
事实上,不管是否提供返回值,程序都会收到一个返回值。SQL Server默认会在完成储存过程是自动返回一个0值。
语法:
RETURN [<interger value to return >]
注:返回值必须为整数。
例:
CREATE PROC spTestReturns
AS
DECLARE @MyMessage varchar(50);
DECLARE @MyOtherMessage varchar(50);
SELECT @MyMessage = 'Hi, it''s that line before the RETURN';
PRINT @MyMessage;
RETURN;
SELECT @MyOtherMessage = 'Sorry, but we won''t get this far';
PRINT @MyOtherMessage;
RETURN;
GO
DECLARE @Return int;
EXEC @Return = spTestReturns;
SELECT @Return;
GO
错误处理:
错误类型:
产生运行时错误并终止代码继续运行的错误。
SQL Server 知道的、但不产生使代码停止运行时错误的错误。这类错误也称为内联错误。
更具逻辑性但在SQL Server中不太引起注意的错误。
处理内联错误:
内联错误是指那些能让SQL Server继续运行但是因为某种原因而不能成功完成指定任务的错误。
利用@@ERROR:
@@ERROR包含了最后执行的T-SQL语句的错误号。如果该值为0,则没有发生错误。@@ERROR根据执行的每条语句接收一个新值。
例:
DECLARE @Error int;
-- Bogus INSERT - there is no PersonID or BusinessEntityID of 0. Either of
-- these could cause the error we see when running this statement.
INSERT INTO Person.BusinessEntityContact
(BusinessEntityID
,PersonID
,ContactTypeID)
VALUES
(0,0,1);
-- Move our error code into safekeeping. Note that, after this statement,
-- @@Error will be reset to whatever error number applies to this statement
PRINT 'The Value of @@ERROR is ' + CONVERT(varchar, @@ERROR);
SELECT @Error = @@ERROR;
-- Print out a blank separator line
PRINT '';
-- The value of our holding variable is just what we would expect
PRINT 'The Value of @Error is ' + CONVERT(varchar, @Error);
-- The value of @@ERROR has been reset - it's back to zero
-- since our last statement (the PRINT) didn't have an error.
PRINT 'The Value of @@ERROR is ' + CONVERT(varchar, @@ERROR);
BEGIN TRY
INSERT OurIFTest(Col1) VALUES (1);
END TRY
BEGIN CATCH
-- Uh oh, something went wrong, see if it's something
-- we know what to do with
PRINT 'Arrived in the CATCH block.';
DECLARE @ErrorNo int,
@Severity tinyint,
@State smallint,
@LineNo int,
@Message nvarchar(4000);
SELECT
@ErrorNo = ERROR_NUMBER(),
@Severity = ERROR_SEVERITY(),
@State = ERROR_STATE(),
@LineNo = ERROR_LINE (),
@Message = ERROR_MESSAGE();
IF @ErrorNo = 2714 -- Object exists error, not likely this time
PRINT 'WARNING: object already exists';
ELSE -- hmm, we don't recognize it, so report it and bail
THROW;
END CATCH
递归简介:
例:
CREATE PROC spFactorial
@ValueIn int,
@ValueOut int OUTPUT
AS
DECLARE @InWorking int;
DECLARE @OutWorking int;
IF @ValueIn > 1
BEGIN
SELECT @InWorking = @ValueIn - 1;
EXEC spFactorial @InWorking, @OutWorking OUTPUT;
SELECT @ValueOut = @ValueIn * @OutWorking;
END
ELSE
BEGIN
SELECT @ValueOut = 1;
END
RETURN;
GO
DECLARE @WorkingOut int;
DECLARE @WorkingIn int;
SELECT @WorkingIn = 5;
EXEC spFactorial @WorkingIn, @WorkingOut OUTPUT;
PRINT CAST(@WorkingIn AS varchar) + ' factorial is ' + CAST(@WorkingOut AS varchar);
GO
注:一旦SQL Server进行了32次的递归,则他会引发错误并停止处理。