Ты — мне, я — тебе!

WHILE @@FETCH_STATUS = 0

BEGIN
— Повторяем
FETCH NEXT FROM WO_cursor INTO @WONumber, @StartDate, @EndDate, @StockItem, @WOQuantity
IF @@FETCH_STATUS = 0
BEGIN
set @OrderTree = @WONumber
set @WOLevel = 1
while ((select MP64041 from MP640100 where ((MP64001=@WONumber) and (MP64041 <> REPLICATE(’ ‘,10)))) is not null)

BEGIN
set @WOLevel = @WOLevel+1
set @StockItem=(select MP64002 from MP640100 where (MP64001=@WONumber) and (MP64041 <> REPLICATE(’ ‘,10)))
set @WONumber=(select MP64041 from MP640100 where (MP64001=@WONumber) and (MP64041 <> REPLICATE(’ ‘,10)))
set @OrderTree = @OrderTree + CHAR(9) + @StockItem + CHAR(9) + @WONumber
set @StockItem=(select MP64002 from MP640100 where (MP64001=@WONumber))
END
— Напечатаем то, что получилось
print CONVERT(nvarchar, @StartDate, 104) + CHAR(9) + CONVERT(nvarchar, @EndDate, 104) + CHAR(9) + @OrderTree + CHAR(9) + @StockItem + CHAR(9) + REPLICATE(CHAR(9),(5-@WOLevel)*2) + @StockItem + CHAR(9) + @WONumber + CHAR(9) + CONVERT(nvarchar, @WOQuantity) + CHAR(9) + CONVERT(nvarchar, @WOLevel)
END
END
— Закрываем и удаляем курсор
CLOSE WO_cursor
DEALLOCATE WO_cursor
— Конец процедуры
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N’[dbo].[AAV_WO_TREE_BY_SO_NUMBER]’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1)
drop procedure [dbo].[AAV_WO_TREE_BY_SO_NUMBER]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[AAV_WO_TREE_BY_SO_NUMBER] (@SONumber nvarchar(10)) 
AS 
— Объявляем переменные
DECLARE @WONumber nvarchar(10), @SOLine nvarchar(6), @lines int, @counter int
set @lines = (select count(*) from (SELECT COUNT(*) AS counted FROM MP640100 WHERE (MP64009 = @SONumber) GROUP BY MP64010) as T1)
set @counter=0
DECLARE SO_cursor CURSOR FOR
   select distinct(MP64010) from MP640100
where (MP64009=@SONumber)
— Открываем курсор
OPEN SO_cursor
— Передаем значения из курсора в переменные
FETCH NEXT FROM SO_cursor INTO @SOLine
print ‘Заказы на работу для строки № ‘+@SOLine+ ‘ Заказа на Продажу № ‘+@SONumber
set @counter=@counter+1
exec dbo.AAV_WO_TREE @SONumber, @SOLine
print »
WHILE @lines>@counter
BEGIN
FETCH NEXT FROM SO_cursor INTO @SOLine
IF @lines>=@counter
BEGIN
print ‘Заказы на работу для строки № ‘+@SOLine+ ‘ Заказа на Продажу № ‘+@SONumber
exec dbo.AAV_WO_TREE @SONumber, @SOLine
print »
END
set @counter=@counter+1
END
CLOSE SO_cursor
DEALLOCATE SO_cursor

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO