使用存储过程利用临时表循环实现多表新增修改update
CREATE PROCEDURE [ dbo] . [ UP_Pack_Detail_Update]
(
@Types varchar ( 100 ) = NULL
)
AS
SET XACT_ABORT ON
BEGIN TRAN
SELECT ROW_NUMBER( ) OVER ( ORDER BY id) i, * INTO
[ pack_detail] where pack_no= @Types
DECLARE @count INT ;
SELECT @count = COUNT ( 1 ) FROM
DECLARE @i INT = 1 ;
WHILE ( @i <= @count )
BEGIN
DECLARE @thickness_grade2 NVARCHAR( 200 ) = NULL ;
DECLARE @thickness_grade NVARCHAR( 200 ) ;
DECLARE @thickness_avg float ;
DECLARE @thickness_avg2 float ;
DECLARE @out_code NVARCHAR( 200 ) ;
DECLARE @pack_no NVARCHAR( 200 ) ;
SELECT @out_code = m_no, @pack_no = pack_no FROM
select @thickness_grade2 = thickness_grade_2, @thickness_grade = thickness_grade_1, @thickness_avg = thickness_avg_1, @thickness_avg2 = thickness_avg_2
from split_detail where out_code= @out_code
print @out_code
print @pack_no
print @thickness_avg
update [ pack_detail] set thickness_grade= @thickness_grade , thickness_grade2= @thickness_grade2 , thickness_avg= @thickness_avg , thickness_avg2= @thickness_avg2
where pack_no= @pack_no and m_no= @out_code
SET @i = @i + 1 ;
SELECT * FROM
END
DROP TABLE
COMMIT TRAN
GO