/*============================================================================== * *Filename:sqllist.sql *Description:sql根据指定条件获取一个字段批量获取数据插入另外一张表字段中 *Version:1.0 *Created:2014.03.13 *Author:liangjw *E-mail:liang
/*==============================================================================
*
* Filename: sqllist.sql
* Description: sql 根据指定条件获取一个字段批量获取数据插入另外一张表字段中
* Version: 1.0
* Created: 2014.03.13
* Author : liangjw
* E-mail : liangjw0504@163.com
* Q Q : 592568532
* Profile Url：http://90ideas.net/
* Company: Copyright (C) Create Family Wealth Power By Peter
*
==============================================================================*/
* 备注信息： 上传部分自己总结的常用方法的封装，有不足和不完美之处，希望大家指出来，愿意一起
* 主要研究erp,cms,crm,b2b,oa等系统和网站的开发，欢迎有共同追求和学的IT人员一起学习和交流。
* 学习和讨论有关asp.net mvc ,Ajax ,jquery ,html/css, xml,sqlserver ,wpf,IIS以及服务器的搭建和安全性相关技术的交流和学习。
/****** 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
