- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- ALTER PROCEDURE [dbo].[qq]
- AS
- --定义获取GUID
- DECLARE @NEWID NVARCHAR(50)
- SET @NEWID= REPLACE(NEWID(),'-','')
- --判断临时表数据是否存在,如果存在则删除临时表
- if OBJECT_ID('tempdb..##project') is not null
- DROP TABLE ##project
- if OBJECT_ID('tempdb..##projectA') is not null
- DROP TABLE ##projectA
- --获取数据源信息
- SELECT id,GuiGe,DocumentNO,OrderNO,WLNO,Color,ISFenMa INTO ##project FROM TB_FLChuKuMx
- SELECT GuiGe,DocumentNO,OrderNO,WLNO,Color,ISFenMa INTO ##projectA FROM TB_FLKuCun WHERE OptType='领料'
- --定义变量
- DECLARE @id INT,@GuiGe NVARCHAR(50)
- DECLARE @DocumentNO NVARCHAR(20),@OrderNO NVARCHAR(20),@WLNO NVARCHAR(20),@Color NVARCHAR(50),@ISFenMa NVARCHAR(20)
- DECLARE @sql NVARCHAR(max)
- SELECT @id = MIN(id) FROM ##project
- WHILE @id IS NOT NULL BEGIN
- SELECT @GuiGe =ISNULL(GuiGe,''),@DocumentNO=DocumentNO,@OrderNO=OrderNO,@WLNO=WLNO,@Color=Color,@ISFenMa=ISFenMa
- FROM ##project WHERE id =@id
- --定义变量
- DECLARE @line INT =0
- SELECT @line=COUNT(0) FROM ##projectA WHERE
- DocumentNO=@DocumentNO AND OrderNO=@OrderNO AND WLNO=@WLNO AND Color=@Color AND ISFenMa=@ISFenMa
- --如果查询出来数据则进行数据修改操作
- IF @line >0 BEGIN
- SET @sql ='update TB_FLKuCun set GuiGe= '''+@GuiGe+''' where
- DocumentNO='''+@DocumentNO+''' and OrderNO='''+@OrderNO+'''
- and WLNO='''+@WLNO+''' and Color='''+@Color+''' and ISFenMa='''+@ISFenMa+''' and OptType=''领料'' '
- PRINT @sql
- --EXEC(@sql)
- END
- SELECT @id = MIN(id) FROM dbo.##project WHERE id >@id
- END
- --判断临时表数据是否存在,如果存在则删除临时表
- if OBJECT_ID('tempdb..##project') is not null
- DROP TABLE ##project
- if OBJECT_ID('tempdb..##projectA') is not null
- DROP TABLE ##projectA
- --该片段来自于http://www.codesnippet.cn/detail/140320149026.html
来源: http://www.codesnippet.cn/detail/140320149026.html