时间:2021-07-01 10:21:17 帮助过:4人阅读
/*============================================================================== * *Filename:sqllist.sql *Description:sql根据指定条件获取一个字段批量获取数据插入另外一张表字段中 *Version:1.0 *Created:2014.03.13 *Author:liangjw *E-mail:liang
/*==============================================================================/****** Object: StoredProcedure [dbo].[getSplitValue] Script Date: 03/13/2014 13:58:12 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[getSplitValue] 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