您所在的位置： &
SQL Server在存储过程中编写事务处理代码的三种方法
SQL Server在存储过程中编写事务处理代码的三种方法
本文我们主要介绍了SQL Server数据库中在存储过程中编写正确的事务处理代码的方法，希望能够对您有所帮助。
SQL Server中数据库事务处理是相当有用的，鉴于很多SQL初学者编写的事务处理代码存往往存在漏洞，本文我们介绍了三种不同的方法，举例说明了如何在存储过程事务处理中编写正确的代码。希望能够对您有所帮助。
在编写SQL Server 事务相关的存储过程代码时，经常看到下面这样的写法：
begin&tran& &update&statement&1&...& &update&statement&2&...& &delete&statement&3&...& &commit&tran&
这样编写的SQL存在很大隐患。请看下面的例子：
create&table&demo(id&int&not&null)& &go& &begin&tran& &insert&into&demo&values&(null) &insert&into&demo&values&(2)& &commit&tran& &go&
执行时会出现一个违反not null 约束的错误信息，但随后又提示(1 row(s) affected)。 我们执行select * from demo 后发现insert into demo values(2) 却执行成功了。 这是什么原因呢? 原来 SQL Server在发生runtime 错误时，默认会rollback引起错误的语句，而继续执行后续语句。
如何避免这样的问题呢？有三种方法：
1. 在事务语句最前面加上set xact_abort on
set&xact_abort&on& &begin&tran& &update&statement&1&...& &update&statement&2&...& &delete&statement&3&...& &commit&tran& &go&
当xact_abort 选项为on 时，SQL Server在遇到错误时会终止执行并rollback 整个事务。
2. 在每个单独的DML语句执行后，立即判断执行状态，并做相应处理。
begin&tran& &update&statement&1&... &if&@@error&&0& &begin&rollback&tran& &goto&labend& &end& &delete&statement&2&...& &if&@@error&&0 &begin&rollback&tran& &goto&labend& &end& &commit&tran& &labend:& &go&
3. 在SQL Server 2005中，可利用 try...catch 异常处理机制。
begin&tran& &begin&try& &update&statement&1&...& &delete&statement&2&...& &endtry& &begin&catch &if&@@trancount&&0& &rollback&tran& &end&catch &if&@@trancount&&0& &commit&tran &go&
下面是个简单的存储过程，演示事务处理过程。
create&procedure&dbo.pr_tran_inproc&as&begin&set&nocount&on& &begin&tran &update&statement&1&...& &if&@@error&&0& &begin&rollback&tran& &return&-1&end& &delete&statement&2&...& &if&@@error&&0& &begin&rollback&tran& &return&-1& &end&commit&tran& &return&0& &end& &go&&
关于SQL Server数据库中在存储过程中编写正确的事务处理代码的方法就介绍到这里了，希望本次的介绍能够对您有所帮助。
原文出处：/sql_article.php?id=。
【编辑推荐】
【责任编辑： TEL：（010）】
关于&&&&的更多文章
不管你有没有准备好，新版SQL Server来了！代号为Denali的下一个
数据库产品
数据库综合
数据库新闻
维基百科将切换到另外一款开源数据库MariaDB
MySQL集群由三部分组成,管理节点、存储节点和SQL节点
MySQL字符串的处理，也就是对于MySQL字符集的处理。主
国产数据库，顾名思义就是中国公司自行开发的数据库产
本书全面翔实地介绍了各种病毒的原理，以操作系统的发展为主线，结合病毒的发展过程来综合分析病毒。在分析工具上，较多地利用了
51CTO旗下网站从存储过程中返回数据
此文章由人工翻译。 将光标移到文章的句子上，以查看原文。
从存储过程中返回数据
SQL Server 2014
有两种方法可以将结果集或数据从过程返回给调用程序：输出参数和返回代码。
如果在过程定义中为参数指定 OUTPUT 关键字，则过程在退出时可将该参数的当前值返回给调用程序。
若要将参数值保存在可在调用程序中使用的变量中，调用程序在执行过程时必须使用 OUTPUT 关键字。 。
以下示例显示有一个输入参数和一个输出参数的过程。
@SalesPerson 参数将接收由调用程序指定的输入值。 SELECT 语句使用传递给输入参数的值来获取正确的 SalesYTD 值。 SELECT 语句还将该值赋给 @SalesYTD 输出参数，该参数在过程退出时将值返回给调用程序。
USE AdventureWorks2012;
IF OBJECT_ID('Sales.uspGetEmployeeSalesYTD', 'P') IS NOT NULL
DROP PROCEDURE Sales.uspGetEmployeeSalesYTD;
CREATE PROCEDURE Sales.uspGetEmployeeSalesYTD
@SalesPerson nvarchar(50),
@SalesYTD money OUTPUT
SET NOCOUNT ON;
SELECT @SalesYTD = SalesYTD
FROM Sales.SalesPerson AS sp
JOIN HumanResources.vEmployee AS e ON e.BusinessEntityID = sp.BusinessEntityID
WHERE LastName = @SalesP
以下示例调用在第一个示例中创建的过程并将从调用的过程返回的输出值保存在 @SalesYTD 变量中，该变量是调用程序的局部变量。
-- Declare the variable to receive the output value of the procedure.
DECLARE @SalesYTDBySalesP
-- Execute the procedure specifying a last name for the input parameter
-- and saving the output value in the variable @SalesYTDBySalesPerson
EXECUTE Sales.uspGetEmployeeSalesYTD
N'Blythe', @SalesYTD = @SalesYTDBySalesPerson OUTPUT;
-- Display the value returned by the procedure.
PRINT 'Year-to-date sales for this employee is ' +
convert(varchar(10),@SalesYTDBySalesPerson);
也可以在执行过程时为 OUTPUT 参数指定输入值。 这将允许过程从调用程序接收值，使用该值更改或执行操作，然后将新值返回给调用程序。 在上一个示例中，可以在程序调用 Sales.uspGetEmployeeSalesYTD 过程前为 @SalesYTDBySalesPerson 变量赋值。 execute 语句将 @SalesYTDBySalesPerson 变量值传递给 @SalesYTD OUTPUT 参数。 然后，在过程主体中，可以将该值用于生成新值的计算。 新值可以通过 OUTPUT 参数重新从过程传回，在过程退出时更新 @SalesYTDBySalesPerson 变量的值。 这常常被称作“传址调用功能”。 如果在调用过程时为参数指定 OUTPUT，而在过程定义中该参数又不是用 OUTPUT 定义的，那么将收到一条错误消息。 但是，在执行过程时，可以执行带有输出参数的过程而不指定 OUTPUT。 这样不会返回错误，但将无法在调用程序中使用输出值。
Transact-SQL 过程只能将 cursor 数据类型用于 OUTPUT 参数。
如果为某个参数指定了 cursor 数据类型，在过程定义中必须为该参数指定 VARYING 和 OUTPUT 关键字。 可以将参数指定为仅限 OUTPUT，但是如果在参数声明中指定了 VARYING 关键字，则数据类型必须为 cursor 并且也必须指定 OUTPUT 关键字。
数据类型不能通过数据库 API（例如 OLE DB、ODBC、ADO 和 DB-Library）绑定到应用程序变量上。
因为必须先绑定 OUTPUT 参数，应用程序才可以执行过程，所以带有 cursor OUTPUT 参数的过程不能通过数据库 API 调用。 只有将 cursor OUTPUT 变量分配给 Transact-SQL 局部 cursor 变量时，才可以通过 Transact-SQL 批处理、过程或触发器调用这些过程。
在执行过程时，以下规则适用于 cursor 输出参数：
对于只进游标，游标的结果集中返回的行只是那些过程执行结束时处于或超出游标位置的行，例如：
在过程中的名为 RS 的 100 行结果集上打开一个非滚动游标。
过程提取结果集 RS 的头 5 行。
过程返回到其调用者。
返回到调用者的结果集 RS 由 RS 的第 6 到 100 行组成，调用者中的游标处于 RS 的第一行之前。
对于只进游标，如果过程退出时游标位于第一行的前面，则整个结果集将返回给调用批处理、过程或触发器。
返回时，游标将位于第一行的前面。
对于只进游标，如果过程退出时游标的位置超出最后一行的结尾，则为调用批处理、过程或触发器返回空结果集。
空结果集与空值不同。
对于可滚动游标，在过程退出时，结果集中的所有行均会返回给调用批处理、过程或触发器。
返回时，游标保留在过程中最后一次执行提取时的位置。
对于任意类型的游标，如果游标关闭，则将 Null 值传递回调用批处理、过程或触发器。
如果将游标指派给一个参数，但该游标从未打开过，也会出现这种情况。
例如，可以在过程中关闭游标，稍后再打开游标，然后将该游标的结果集返回给调用批处理、过程或触发器。
在下面的示例中，创建了使用 cursor 数据类型指定输出参数 @currency_cursor 的过程。
然后在批处理中调用该过程。
首先，创建以下过程，在 Currency 表上声明并打开一个游标。
USE AdventureWorks2012;
IF OBJECT_ID ( 'dbo.uspCurrencyCursor', 'P' ) IS NOT NULL
DROP PROCEDURE dbo.uspCurrencyC
CREATE PROCEDURE dbo.uspCurrencyCursor
@CurrencyCursor CURSOR VARYING OUTPUT
SET NOCOUNT ON;
SET @CurrencyCursor = CURSOR
FORWARD_ONLY STATIC FOR
SELECT CurrencyCode, Name
FROM Sales.C
OPEN @CurrencyC
接下来，执行一个批处理，声明一个局部游标变量，执行上述过程以将游标赋值给局部变量，然后从该游标提取行。
USE AdventureWorks2012;
DECLARE @MyCursor CURSOR;
EXEC dbo.uspCurrencyCursor @CurrencyCursor = @MyCursor OUTPUT;
WHILE (@@FETCH_STATUS = 0)
FETCH NEXT FROM @MyC
CLOSE @MyC
DEALLOCATE @MyC
过程可以返回一个整数值（称为“返回代码”），以指示过程的执行状态。
使用 RETURN 语句指定过程的返回代码。 与 OUTPUT 参数一样，执行过程时必须将返回代码保存到变量中，才能在调用程序中使用返回代码值。 例如，数据类型 int 的赋值变量 @result 用于存储来自过程 my_proc 的返回代码，如：
EXECUTE @result = my_
返回代码通常用在过程内的控制流块中，以便为每种可能的错误情况设置返回代码值。 可以在 Transact-SQL 语句后使用 @@ERROR 函数，来检测该语句执行过程中是否有错误发生。 下面的示例显示了带有错误处理设置（为各种错误设置特殊返回代码值）的 usp_GetSalesYTD 过程。 下表显示了由过程分配给每个可能错误的整数值，以及每个值的相应含义。 返回代码值含义0成功执行。1未指定所需参数值。2指定参数值无效。3获取销售额数值时出错。4该销售人员的销售额数值为 NULL。
USE AdventureWorks2012;
IF OBJECT_ID('Sales.usp_GetSalesYTD', 'P') IS NOT NULL
DROP PROCEDURE Sales.usp_GetSalesYTD;
CREATE PROCEDURE Sales.usp_GetSalesYTD
@SalesPerson nvarchar(50) = NULL,
-- NULL default value
@SalesYTD money = NULL OUTPUT
-- Validate the @SalesPerson parameter.
IF @SalesPerson IS NULL
PRINT 'ERROR: You must specify a last name for the sales person.'
-- Make sure the value is valid.
IF (SELECT COUNT(*) FROM HumanResources.vEmployee
WHERE LastName = @SalesPerson) = 0
-- Get the sales for the specified name and
-- assign it to the output parameter.
SELECT @SalesYTD = SalesYTD
FROM Sales.SalesPerson AS sp
JOIN HumanResources.vEmployee AS e ON e.BusinessEntityID = sp.BusinessEntityID
WHERE LastName = @SalesP
-- Check for SQL Server errors.
IF @@ERROR && 0
-- Check to see if the ytd_sales value is NULL.
IF @SalesYTD IS NULL
-- SUCCESS!!
-- Run the stored procedure without specifying an input value.
EXEC Sales.usp_GetSalesYTD;
-- Run the stored procedure with an input value.
DECLARE @SalesYTDForSalesPerson money, @ret_
-- Execute the procedure specifying a last name for the input parameter
-- and saving the output value in the variable @SalesYTD
EXECUTE Sales.usp_GetSalesYTD
N'Blythe', @SalesYTD = @SalesYTDForSalesPerson OUTPUT;
PRINT N'Year-to-date sales for this employee is ' +
CONVERT(varchar(10), @SalesYTDForSalesPerson);
下面的示例创建了处理从 usp_GetSalesYTD 过程返回的返回代码的程序。
-- Declare the variables to receive the output value and return code
-- of the procedure.
DECLARE @SalesYTDForSalesPerson money, @ret_
-- Execute the procedure with a title_id value
-- and save the output value and return code in variables.
EXECUTE @ret_code = Sales.usp_GetSalesYTD
N'Blythe', @SalesYTD = @SalesYTDForSalesPerson OUTPUT;
Check the return codes.
IF @ret_code = 0
PRINT 'Procedure executed successfully'
-- Display the value returned by the procedure.
PRINT 'Year-to-date sales for this employee is ' + CONVERT(varchar(10),@SalesYTDForSalesPerson)
ELSE IF @ret_code = 1
PRINT 'ERROR: You must specify a last name for the sales person.'
ELSE IF @ret_code = 2
PRINT 'EERROR: You must enter a valid last name for the sales person.'
ELSE IF @ret_code = 3
PRINT 'ERROR: An error occurred getting sales value.'
ELSE IF @ret_code = 4
PRINT 'ERROR: No sales recorded for this employee.'
您对此内容的反馈非常重要。请告诉我们您的想法。
更多反馈？
1500 个剩余字符
我们非常感谢您的反馈。
开发人员中心SQLServer存储过程返回值总结 -
- ITeye技术网站
博客分类：
存储过程没有返回值的情况(即存储过程语句中没有return之类的语句)
用方法 int count = ExecuteNonQuery(..)执行存储过程其返回值只有两种情况
(1)假如通过查询分析器执行该存储过程，在显示栏中假如有影响的行数，则影响几行count就是几
(2)假如通过查询分析器执行该存储过程，在显示栏中假如显示'命令已成功完成。'则count = -1;在显示栏中假如有查询结果，则count = -1
总结：A.ExecuteNonQuery()该方法只返回影响的行数，假如没有影响行数，则该方法的返回值只能是-1，不会为0。
B.不论ExecuteNonQuery()方法是按照CommandType.StoredProcedure或者CommandType.Text执行，其效果和A一样。
---------------------------------------------------------------------------------------------------------------------------------------------------
获得存储过程的返回值--通过查询分析器获得
(1)不带任何参数的存储过程(存储过程语句中含有return)
---创建存储过程
CREATE PROCEDURE
testReturn
return 145
---执行存储过程
DECLARE @RC int
exec @RC=testReturn
select @RC
查询结果为145
(2)带输入参数的存储过程(存储过程语句中含有return)
---创建存储过程
create procedure sp_add_table1
@in_name varchar(100),
@in_addr varchar(100),
@in_tel varchar(100)
if(@in_name = '' or @in_name is null)
insert into table1(name,addr,tel) values(@in_name,@in_addr,@in_tel)
---执行存储过程
&1&执行下列，返回1
declare @count int exec @count = sp_add_table1 '','中三路','123456' select @count
&2&执行下列，返回0
declare @count int exec @count = sp_add_table1 '','中三路','123456' select @count
查询结果不是0就是1
(3)带输出参数的存储过程(存储过程中可以有return可以没有return)
---创建存储过程
create procedure sp_output
@output int output
set @output = 121
---执行存储过程
&1&执行下列，返回121
declare @out int
exec sp_output @out output
select @out
&2&执行下列，返回1
declare @out int
declare @count int
exec @count = sp_output @out output
select @count
有return，只要查询输出参数，则查询结果为输出参数在存储过程中最后变成的值；只要不查询输出参数，则查询结果为return返回的值
---创建存储过程
create procedure sp_output
@output int output
set @output = 121
---执行存储过程
&1&执行下列，返回121
declare @out int
exec sp_output @out output
select @out
&2&执行下列，返回0
declare @out int
declare @count int
exec @count = sp_output @out output
select @count
没有return，只要查询输出参数，则查询结果为输出参数在存储过程中最后变成的值；只要不查询输出参数，则查询结果为0
(1)存储过程共分为3类：
A.返回记录集的存储过程---------------------------其执行结果是一个记录集，例如：从数据库中检索出符合某一个或几个条件的记录
B.返回数值的存储过程(也可以称为标量存储过程)-----其执行完以后返回一个值，例如：在数据库中执行一个有返回值的函数或命令
C.行为存储过程-----------------------------------用来实现数据库的某个功能，而没有返回值，例如：在数据库中的更新和删除操作
(2)含有return的存储过程其返回值为return返回的那个值
(3)没有return的存储过程，不论执行结果有无记录集，其返回值是0
(4)带输出参数的存储过程：假如有return则返回return返回的那个值，假如要select输出参数，则出现输出参数的值，于有无return无关
---------------------------------------------------------------------------------------------------------------------------------------------------
3.获得存储过程的返回值--通过程序获得
---------------------------------------------------------------------------------------------------------------------------------------------------
SqlParameter[] cmdParms = { .. ,new SqlParameter("@return",SqlDbType.Int)};
cmdParms[cmdParms.Length - 1].Direction = ParameterDirection.ReturnV或者
cmdParms[cmdParms.Length - 1].Direction = ParameterDirection.Output或者
cmdParms[cmdParms.Length - 1].Direction = ParameterDirection.I
得到返回值 object bj = cmdParms[cmdParms.Length - 1].V
jackleechina
浏览: 89005 次
来自: 上海
还是会出bug，加减乘除都会有精度问题，根本的办法是重写toF ...在sql Server中~如何利用RETURN返回存储过程的值~_百度知道
在sql Server中~如何利用RETURN返回存储过程的值~
create proc sp_CourseFeedBack_Insert(----报班人数@CourseReg int=0, ---退班人数@CourseWithDrawNum int=0,---赠班人数@CourseGive int=0, ---缺勤人数@CourseAbsence int=0, ---实际上课人数@CourseReal int=0, ---调查表份数@QuestionnaireNo int=0, ---平均分@AverageScore decimal,---退班原因@CourseQuitReason varchar(100)='',---备注@CourseComment varchar(100)='',---图片地址@CourseImageURL varchar(100)='',---学员意见@CourseStuSuggest varchar(100)='',@TeacherClassID int)as begindeclare @StateID intdeclare @CourseFeedBackID int--取出@stateIDexec sp_StateTmp_Insert @StateID=@StateID outputinsert into tb_Course_FeedBack(StateID,TeacherClassID ,CourseReg, CourseWithDrawNum,CourseGive, CourseAbsence, CourseReal, QuestionnaireNo, AverageScore,CourseQuitReason,CourseStuSuggest,CourseComment,CourseImageURL,CourseSubmitTime,isFilled)values(@StateID,@TeacherClassID,@CourseReg, @CourseWithDrawNum,@CourseGive, @CourseAbsence, @CourseReal, @QuestionnaireNo, @AverageScore,@CourseQuitReason,@CourseStuSuggest,@CourseComment,@CourseImageURL,getdate(),0)SET @CourseFeedBackID = SCOPE_IDENTITY()return @CourseFeedBackIDend--创建返回stateID的存储过程create Proc sp_StateTmp_Insert@StateID int outputasbegininsert into tb_State(isSend) values(0)SET @StateID = SCOPE_IDENTITY()end
其他类似问题
为您推荐：
您可能关注的推广
存储过程的相关知识
等待您来回答
下载知道APP
随时随地咨询
出门在外也不愁}