SQLServer存储过程中有事物，怎样取得执行成功与否的标记，的return和返回值问题，有什么区别

[WYhack]

实话说对sqlserver的存储过程并不熟悉，先求教，希望指点，给点资料也行，谢谢

CREATE PROCEDURE pro
	-- Add the parameters for the stored procedure here
	@i nvarchar(20),
	@j int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
--事物
    -- Insert statements for procedure here
	--事物
		update table1 
		if(@@error>0)
		--回滚
	
	--事物提交
	
	--事物
		delete from table2 
		if(@@error>0)
		--回滚
	--事物提交
	
	--异常回滚
	
--事物提交	
END
GO

类似以上的存储过程，更新一个表，同时删除另外一个表，放在一个事物中，我先要执行成功与否的标记，该怎么写“返回值”，是写return还是写返回值？

能写return的话该怎么写，该怎么取得该return的标记，返回值又怎么写（设定），

写return和返回值又什么区别？


再问一下：设计表的时候，做删除数据的时候，应该是标记一个状态好还是做物理删除好？

[gogodiy]

最简单的办法：

CREATE PROCEDURE pro
@i nvarchar(20),
@j int,
@Result BIGINT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
	
	SET XACT_ABORT ON
	
	BEGIN TRANSACTION
	
    update table1 

    delete from table2 

	COMMIT TRANSACTION
	
	SET @Result=1  --执行成功
END
GO

[chuanzhang5687]

ruturn就可以！

[AcHerat]

做两个输出字段，来标记更新和删除具体哪个事务有异！

[chuanzhang5687]

第二个，可以添加两列 一个是逻辑删除，一个是物理删除！

[AcHerat]

再问一下：设计表的时候，做删除数据的时候，应该是标记一个状态好还是做物理删除好？

如果删除掉的数据以后要用到，那么可以建立一张历史表，把删除的数据放进历史表，然后删除这条数据

如果删除了就不管这条数据，那么直接物理删除。

[NBDBA]

我先要执行成功与否的标记，该怎么写“返回值”
每句执行后判断@@error全局变量（2000）或者用Try...Catch结构

能写return的话该怎么写，该怎么取得该return的标记，返回值又怎么写（设定），
可以return,return只能返回int，怎么定义你自己决定，与程序有约定就可以

写return和返回值又什么区别？
返回值可返回多种类型数据，return只能int

[AcHerat]

CREATE PROCEDURE pro
    -- Add the parameters for the stored procedure here
    @i nvarchar(20),
    @j int,
	@update int output,
	@delete int output
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
--事物
    -- Insert statements for procedure here
    --事物
        update table1 
        if(@@error>0)
		begin
		set @update = 1
        --回滚
		end
		set @update = 0
    --事物提交
    
    --事物
        delete from table2 
        if(@@error>0)
		begin
		set @delete = 1
        --回滚
		end
		set @delete = 0
    --事物提交
    
    --异常回滚
    
--事物提交    
END
GO

这样应该也能获取到执行成功与否的标记。

[NBDBA]

再问一下：设计表的时候，做删除数据的时候，应该是标记一个状态好还是做物理删除好？

这要看需求，如果你的数据是财务数据，你可能需要做逻辑删除，以便查数，如果不重要数据，直接删除克节省空间并提高性能

[快溜]

create PROCEDURE pro
    @i nvarchar(20),
    @j varchar(5) output
AS
BEGIN
    declare @num datetime
    begin try
      begin tran
        set @num=@i
        set @j='成功'
        commit
    end try    
     begin catch
         rollback
         set @j='失败'
     end catch
END
GO

declare @num varchar(5) 

exec pro 'aa',@num output
select @num
/*失败*/

exec pro '20110811',@num output

select @num

/*成功*/

[rmini]

不太熟悉，作为学习资料收藏

[wdmxznl]

[Quote=引用 6 楼 acherat 的回复:]

SQL code

CREATE PROCEDURE pro
-- Add the parameters for the stored procedure here
@i nvarchar(20),
@j int,
@update int output,
@delete int output
AS
BEGIN
-- SET NOCOUNT ON ……
[/Quote]


像这样的事物处理方式，假如第一个sql执行成功，后边的一个执行失败，前面执行成功的会回滚么？
为什么？

create proc aaa  
参数...  
as  
begin tran
SQL语句1  
if @@error<>0
  begin
  rollback tran
  return -1
  end	
SQL语句2  
if @@error<>0
  begin
  rollback tran
  return -1
  end	
SQL语句3  
if @@error<>0
  begin
  rollback tran
  return -1
  end	

commit tran  
go  

[wdmxznl]

SQL语句3  
if @@error<>0
  begin
  rollback tran
  return -1 ----这里的renturn的值怎么取得到？
  end    

[geniuswjt]

if @@error<>0
  begin
set @i=-1
  rollback tran
  return @i ----这里的renturn的值怎么取得到？
  end  

[Quote=引用 11 楼 wdmxznl 的回复:]
SQL code
SQL语句3
if @@error<>0
begin
rollback tran
return -1 ----这里的renturn的值怎么取得到？
end
[/Quote]

[WYhack]

[Quote=引用 6 楼 acherat 的回复:]

SQL code

CREATE PROCEDURE pro
-- Add the parameters for the stored procedure here
@i nvarchar(20),
@j int,
@update int output,
@delete int output
AS
BEGIN
-- SET NOCOUNT ON ……
[/Quote]

        begin
        set @update = 1
        --回滚
        end

这里能不能直接写 set @update = 1？begin……end的作用是什么？

[AcHerat]

create table tb(id int identity(1,1),ic int unique)
insert into tb select 2
go

create proc pro
    @i int,
    @j int output,
	@k int output
AS
BEGIN
    begin try
      begin tran
        insert into tb select @i
		set @j = 0
        commit
    end try    
     begin catch
         rollback
         set @j=1
     end catch

    begin try
      begin tran
        update tb set ic = 1 where id = @i
		set @k = 0
        commit
    end try    
     begin catch
         rollback
         set @k=1
     end catch
END
GO

declare @m int,@n int
exec pro 1,@m output,@n output
select * from tb
select @m,@n

drop proc pro
drop table tb

/***********

id          ic
----------- -----------
1           2
2           1

(2 行受影响)

            
----------- -----------
0           1

(1 行受影响)