SqlServer 简单存储过程记录，循环清洗数据

记录一下此存储过程以备复制使用

--DROP PROC updateCsr

create proc updateCsr

as

begin

DECLARE @unionId uniqueidentifier

DECLARE @pid uniqueidentifier

DECLARE @gid uniqueidentifier

DECLARE @parentID uniqueidentifier

DECLARE @nodeLevel int

DECLARE @code nvarchar(30)

DECLARE @name nvarchar(50)

DECLARE @spelling nvarchar(200)

DECLARE @shortSpelling nvarchar(50)

DECLARE @hasChildren bit

DECLARE @sortID int

DECLARE @profitScale DECIMAL(18,2)

DECLARE unionCursor Cursor for SELECT  ID  FROM UnionInfo WHERE  ID = '637E877D-B430-47D4-AA82-00827E772D71'

   Open unionCursor

Fetch Next From unionCursor Into @unionId

While (@@Fetch_Status = 0)    

       begin    

Declare oneTypeCursor Cursor for SELECT id,parentid,nodelevel,code,name,spelling,shortspelling,HasChildren,sortid,profitScale FROM GoodsCategorys WHERE Grade = 0  AND NodeLevel = 1  and UnionID = 'F84CE2FE-A8C0-4FE9-8781-6C44ADCE315E';

               Open oneTypeCursor

Fetch Next From oneTypeCursor Into @gid,@parentID,@nodeLevel,@code,@name,@spelling,@shortSpelling,@hasChildren,@sortID,@profitScale

While (@@Fetch_Status = 0)    

                   begin  

set @pid = NEWID()

print 1

INSERT  INTO GoodsCategorys VALUES(@pid,null,@nodeLevel,@code,@unionId,@name,@spelling,@shortSpelling,@hasChildren,0,@sortID,@profitScale)

Fetch Next From oneTypeCursor Into @gid,@parentID,@nodeLevel,@code,@name,@spelling,@shortSpelling,@hasChildren,@sortID,@profitScale

                   end  

               Close oneTypeCursor  

               Deallocate oneTypeCursor  

Fetch Next From unionCursor into @unionId

   end  

 Close unionCursor  

 Deallocate unionCursor  

end

go

exec updateCsr

go