大家帮我看看这个储存过程中的事务这样写有问题吗？

[ldw701]

DECLARE @list_id bigint
DECLARE @region varchar(2)
DECLARE @country varchar(2)
DECLARE @regfrom datetime
DECLARE @regto datetime
DECLARE @isworkingon varchar(2)
DECLARE @isward varchar(2)
DECLARE @email varchar(128)

DECLARE insert_cursor CURSOR FOR SELECT list_id,region,country,regfrom,regto,isworkingon,isaward,email FROM lists WHERE list_type_id=1
DECLARE @sql varchar(8000)
OPEN insert_cursor

FETCH NEXT FROM insert_cursor INTO @list_id,@region,@country,@regfrom,@regto,@isworkingon,@isward,@email

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql=''
SET @sql = @sql+'INSERT INTO query_list_members (list_id,solver_id) '
SET @sql = @sql+'SELECT '+CAST(@list_id as varchar)+',a.id from solvers a INNER JOIN accounts b ON a.id=b.id INNER JOIN addresses c ON b.address_id=c.id INNER JOIN countries d ON c.country_id=d.id WHERE 1=1 '
IF @region IS NOT NULL
SET @sql = @sql + 'AND d.region_id='''+@region+''''
IF @country IS NOT NULL
SET @sql = @sql + ' AND d.id='''+@country+''''
IF @regfrom IS NOT NULL
SET @sql = @sql + ' AND b.date_created >='''+CONVERT(varchar(10),@regfrom,120)+''''
IF @regto IS NOT NULL
SET @sql = @sql + ' AND b.date_created <='''+CONVERT(varchar(10),@regTo,120)+''''
IF @isworkingon='Y'
SET @sql = @sql + ' AND EXISTS (SELECT solver_id FROM challenge_engagements WHERE solver_id=a.id)'
ELSE IF @isworkingon='N'
SET @sql = @sql + ' AND NOT EXISTS (SELECT solver_id FROM challenge_engagements WHERE solver_id=a.id)'
IF @isward='Y'
SET @sql = @sql + ' AND EXISTS (SELECT solver_id FROM submissions WHERE solver_id=a.id)'
ELSE IF @isward='N'
SET @sql = @sql + ' AND NOT EXISTS (SELECT solver_id FROM submissions WHERE solver_id=a.id)'
IF @email IS NOT NULL
SET @sql = @sql + ' AND b.EMAIL LIKE ''%'+@email+'%'''
print @sql
BEGIN TRAN ---就是这句
DELETE query_list_members WHERE list_id=@list_id
IF @@ERROR=0
BEGIN
EXEC(@sql)
IF @@ERROR=0
COMMIT TRAN
ELSE
ROLLBACK TRAN
END
ELSE
ROLLBACK TRAN
FETCH NEXT FROM insert_cursor INTO @list_id,@region,@country,@regfrom,@regto,@isworkingon,@isward,@email
END
CLOSE insert_cursor
DEALLOCATE insert_cursor

我是问begin tran应该放在while外面还是里面啊？

[ldw701]

怎么没人来啊

[hellowork]

放在循环例外与楼主的需求有关.
如果希望整个循环过程中任意一次循环中操作出错了就回滚,请将事务放在循环之外,也就是说将循环删除放到一个事务中.
如果希望整个循环过程中每次操作出错了只回滚本次操作,然后继续循环处理则将事务放到循环内,也就是说为每次循环都启用一个事务,象现在这样.

[pengda1i]

用动态语句这个事务处理应该有问题，因为sql server认为动态语句执行的是另一个连接

[hellowork]

抱歉,错别字更正一下:
放在循环[例]外应为放在循环[里]外

[ldw701]

TO pengda1i(冒牌大力 V0.3)
那要怎么写才能保证这2个语句在同一个事务种执行呢？

[Well]

首先你要知道你的处理，你的业务需求。

也就是在那里出现异常就回滚。。