存储过程的问题

[psigw1314]

CREATE PROCEDURE TestAAA
AS
BEGIN
	SET NOCOUNT ON;
	begin transaction
	declare @errorSum int
	insert into TB_B(ID,NAME,[TIME])
	SELECT ID,NAME,[TIME] from TB_A
	where [TIME]<GETDATE()-1
	set @errorSum+=@@error
	delete from TB_A 
	where [TIME]<GETDATE()-1
	set @errorSum+=@@error
	if @errorSum>0
		begin
		print 'error'
		rollback transaction
		end
	else
		begin
		print 'ok'
		commit transaction
		end
END
GO

这个存储过程的第一句SQL执行了,为什么delete那句就不执行了呢,求指教.

[天-笑]

--试试
CREATE PROCEDURE TestAAA
AS
BEGIN
    SET NOCOUNT ON;
    begin transaction
    declare @errorSum int
    
    insert into TB_B(ID,NAME,[TIME])
    SELECT ID,NAME,[TIME] from TB_A
    where [TIME]<DATEDIFF(DAY,-1,GETDATE())
    
    set @errorSum =@errorSum + @@error
    
    delete from TB_A 
    where [TIME]<DATEDIFF(DAY,-1,GETDATE())
    
    set @errorSum = @errorSum + @@error
    
    if @errorSum>0
        begin
        print 'error'
        rollback transaction
        end
    else
        begin
        print 'ok'
        commit transaction
        end
END
GO

[psigw1314]

delete 语句执行了, 但是条件不对了...我要删除前一个礼拜的数据怎么弄.

[开着拖拉机泡妞]

CREATE PROCEDURE TestAAA
AS
BEGIN
    SET NOCOUNT ON;
    begin transaction
    declare @errorSum int
    insert into TB_B(ID,NAME,[TIME])
    SELECT ID,NAME,[TIME] from TB_A
    where [TIME]<dateadd(dd,-1,GETDATE())
    set @errorSum+=@@error
    delete from TB_A 
    where [TIME]<dateadd(dd,-1,GETDATE())
    set @errorSum+=@@error
    if @errorSum>0
        begin
        print 'error'
        rollback transaction
        end
    else
        begin
        print 'ok'
        commit transaction
        end
END
GO

[开着拖拉机泡妞]

CREATE PROCEDURE TestAAA
AS
BEGIN
    SET NOCOUNT ON;
    begin transaction
    declare @errorSum int
    insert into TB_B(ID,NAME,[TIME])
    SELECT ID,NAME,[TIME] from TB_A
    where [TIME]<dateadd(dd,-1,GETDATE())
    set @errorSum+=@@error
    delete from TB_A 
    where [TIME] between dateadd(dd,-7,GETDATE()) and dateadd(dd,-1,GETDATE())
    set @errorSum+=@@error
    if @errorSum>0
        begin
        print 'error'
        rollback transaction
        end
    else
        begin
        print 'ok'
        commit transaction
        end
END
GO

--前一个礼拜的数据，也就是说从今天往前推七天的数据？

[天-笑]

[Quote=引用 2 楼 的回复:]
delete 语句执行了, 但是条件不对了...我要删除前一个礼拜的数据怎么弄.
[/Quote]

 delete from TB_A 
    where [TIME] <= dateadd(day,-7,getdate()) --删除7天前数据