这是不用游标的大概思路declare @tab table(ID int identity(1,1), col1 varchar(10),col2 varchar(10)) insert into @tab select id, col1,col2 --需要传给存储过程的字段 from table1
declare @i int declare @j int set @i = 0
select @j = MAX(ID) from @tab BEGIN TRANSACTION while @i < @j begin set @i = @i+1 set @col1= null set @col2= null select @col1=col1, @col2=col2 from @tab where ID = @i exec sp_change @col1,@col2 --这个地方根据你的存储过程有output输出值,改下后面的写法 IF @@ERROR <>0 BEGIN PRINT '处理不成功' ELSE PRINT '处理成功' END COMMIT TRANSACTION GO
用游标的大概思路DECLARE @col1 VARCHAR(10), @col1 VARCHAR(10) BEGIN DECLARE c_table1 CURSOR FOR SELECT col1,col2 FROM table1 OPEN c_table1 FETCH NEXT FROM c_table INTO @col1,@col2 WHILE @@fetch_status=0 BEGIN TRANSACTION PRINT @col1,@col2 EXEC sp_change @col1,@col2 IF @@ERROR=0 PRINT '处理成功' FETCH NEXT FROM c_table INTO @col1,@col2 ELSE ROLLBACK TRANSACTION CLOSE c_table DEALLOCATE c_table END GO
insert into @tab
select id, col1,col2 --需要传给存储过程的字段
from table1
declare @i int
declare @j int
set @i = 0
select @j = MAX(ID) from @tab
BEGIN TRANSACTION
while @i < @j begin
set @i = @i+1
set @col1= null
set @col2= null select @col1=col1, @col2=col2 from @tab where ID = @i
exec sp_change @col1,@col2 --这个地方根据你的存储过程有output输出值,改下后面的写法
IF @@ERROR <>0
BEGIN
PRINT '处理不成功'
ELSE
PRINT '处理成功'
END
COMMIT TRANSACTION
GO
@col1 VARCHAR(10)
BEGIN
DECLARE c_table1 CURSOR FOR
SELECT col1,col2 FROM table1
OPEN c_table1
FETCH NEXT FROM c_table INTO @col1,@col2
WHILE @@fetch_status=0
BEGIN TRANSACTION
PRINT @col1,@col2
EXEC sp_change @col1,@col2
IF @@ERROR=0
PRINT '处理成功'
FETCH NEXT FROM c_table INTO @col1,@col2
ELSE
ROLLBACK TRANSACTION
CLOSE c_table
DEALLOCATE c_table
END
GO
其实 放在程序中处理也可以的。
就是这里怎么得到返回结果,因为结果是文字提示,要存到table1里,方便以后查看。
EXEC sp_change @col1,@col2存储过程中返回值是这样的
@sResultMsg varchar(100) OUTPUT
SET @sResultMsg= '返回各种结果';
这样执行存储过程后,怎么把结果用变量保存?
EXEC sp_change @col1,@col2
exec sp_ChangeJqm '98f00b20','12345678',0, @out output
select @out