Executing a Function for every record in a File 1

[crystalreporting]

I have the following statement that creates a file from the output of a function - it works perfectly:

insert into Flattened_BOM_SQL (low_level,Par_item_no,Comp_Item_no,Qty_Per_Parent,Extended_Qty_Per_Parent,Processed,sort_order)
select low_level,Par_item_no,Comp_Item_no,Qty_Per_Parent,Extended_Qty_Per_Parent,Processed,sort_order from fn_Flatten_BOM('WIDGET') ORDER BY sort_order

In the example above, this would run for the item 'WIDGET' which is a record in a inventory table. I'd like to create a statement that I can schedule nightly that will read all of the items in the inventory table (Item_Master_SQL) and create the Flattened_BOM_SQL table using the code above.

What is the structure I need in order to accomplish this?

Peter Shirley

http://www.srhconsulting.com

 

[mrdenny]

If you are asking how to pull a list of the item's from the Inventory table then run the listed commend for each one of them, you'll need to use a cursor.

Something like this should do the trick.

declare @Item varchar(50)
declare cur CURSOR for select distinct ItemName from Item_Master_SQL with (nolock)
open cur
fetch next from cur into @Item
while @@FETCH_STATUS = 0
BEGIN
    insert into Flattened_BOM_SQL (low_level,Par_item_no,Comp_Item_no,Qty_Per_Parent,Extended_Qty_Per_Parent,Processed,sort_order)
    select low_level,Par_item_no,Comp_Item_no,Qty_Per_Parent,Extended_Qty_Per_Parent,Processed,sort_order from fn_Flatten_BOM(@Item) ORDER BY sort_order
    fetch next from cur into @Item
END
close cur
deallocate cur

Put that code within a stored procedure, and then schedule the procedure to run nightly (or at what ever schedule is needed).

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[crystalreporting]

Thanks - worked like a charm.

Peter Shirley

http://www.srhconsulting.com

 

[mrdenny]

No problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com