running stored procedure in a loop to get Top level Demand(s) form a Supply

Question

running stored procedure in a loop to get Top level Demand(s) form a Supply

skb 44459

SSCarpal Tunnel

Points: 4768
More actions
August 29, 2017 at 7:51 am

#323321

i would like to create a stored procedure to find the Top Level Demand for a Supply .
I have a stored procedure which gives the demand for the passed Supply Matl Tag. Aim is to loop through this stored procedure to find the top level Demand.
Note that Each Supply could have one or multiple demands.
So at the end we might come up with multiple Top Level Demand for a given supply.
the stored procedure accept one parameter Supply ID :
exec GetSupplyDemand 'SupplyID'
1.
create table tt-matl
DemandID as int
2. create table by executing stored procedure GetSupplyDemand
insert into tt-matl
exe c GetSupplyDemand matltag = 100
3. Loop through tt-matl table and execute the stored procedure GetSupplyDemand

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110640 More actions · Answer 1

Okay, but you used the words "top level" without defining exactly what that means in your environment. We can't even guess as you've provided no useful context. Assuming you have a stored procedure that gets all the demand values for a given Supply Material Tag value, doesn't really give us enough to go on. From those available values, what makes any given value "top level" ?

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 2

As Steve noted, your terminology makes no sense from the outside. We don't know your business, so the Supply Mtl Tag and demand have no context. We'd need some explanation of what these terms mean for the data relationships.

However, your text seems to indicate that you want to work in a serial, row by row fashion. In SQL , we want to think about working with a big set of data all at once.

skb 44459 SSCarpal Tunnel Points: 4768 More actions · Answer 3

Here is the Example
Customer Order Line :
ORD00001 - 1
                                         --JOB- JOB0001
                                              ----JOB-JOB0002
                                                  ------JOB-JOB0003
                                                       --------JOB-JOB0004
                                                             ----------MATERIAL-Matl01

ORD09999 - 1
                                         --JOB- JOB0001
                                              ----JOB-JOB0002
                                                  ------JOB-JOB0003
                                                       --------JOB-JOB0004
                                                             ----------MATERIAL-Matl01

If I want to find the Top Level Demand for MATERIAL-Matl01 , My Result should be - ORD00001-1, ORD09999-1

if I run exec GetSupplyDemand 'MATERIAL-Matl01' I will get result 'JOB-JOB0004'.

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 4

Your indentation doesn't make sense from a db perspective. Are these separate lines? a separate table of data? What is a demand, and what does "top" mean? Is this ordering by largest job number? Date?

The two results you give don't make sense. How do you arrive at the rules for producing those results?

skb 44459 SSCarpal Tunnel Points: 4768 More actions · Answer 5

I don't know the DB detail. The Stored procedure gives the list of demand for the supplied SupplierID. We just keep looping thru until we don't find Demand for the Supply and that becomes Top Level Demand.
In our examples :
Customer Order Line :
ORD00001 - 1
--JOB- JOB0001
----JOB-JOB0002
------JOB-JOB0003
--------JOB-JOB0004
----------MATERIAL-Matl01
So the program should loop for
exec GetSupplyDemand 'MATERIAL-Matl01' - Output will be JOB-JOB0004
exec GetSupplyDemand 'JOB-JOB0004'- Output will be JOB-JOB0003
exec GetSupplyDemand 'JOB-JOB0003'- Output will be JOB-JOB0002
exec GetSupplyDemand 'JOB-JOB0002'- Output will be JOB-JOB0001
exec GetSupplyDemand 'JOB-JOB0001'- Output will be ORD00001 - 1.
exec GetSupplyDemand 'ORD00001 - 1'- Output will be NULL

So program will stop here and give Top Level Demand as 'ORD00001 - 1'

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 6

If that's all you have and can't write code, then:

declare @top varchar(1000) = 'MATERIAL-Matl01', @Last varchar(1000) = '' while @result is not null begin exec @result = GetSupplyDemand @top if @result is null break; else select @top = @result; end

skb 44459 SSCarpal Tunnel Points: 4768 More actions · Answer 7

Thanks for the reply. The problem is the output of the stored procedure is a dataset and it could have multiple demand. If there a one demand for a supplyid the. Code will work fine.

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 8

If there are multiple items, how are they returned? Is this a text stream, or are these separate rows being returned? The output is a datatable/result set of some sort, with rows and columns. It can probably be parsed, but that's a highly inefficient use of SQL Server resources. Your front end application code is better suited to handle some of this.

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 9

skb 44459 - Tuesday, August 29, 2017 11:12 AM
I don't know the DB detail. The Stored procedure gives the list of demand for the supplied SupplierID. We just keep looping thru until we don't find Demand for the Supply and that becomes Top Level Demand.
In our examples :
Customer Order Line :
ORD00001 - 1
--JOB- JOB0001
----JOB-JOB0002
------JOB-JOB0003
--------JOB-JOB0004
----------MATERIAL-Matl01
So the program should loop for
exec GetSupplyDemand 'MATERIAL-Matl01' - Output will be JOB-JOB0004
exec GetSupplyDemand 'JOB-JOB0004'- Output will be JOB-JOB0003
exec GetSupplyDemand 'JOB-JOB0003'- Output will be JOB-JOB0002
exec GetSupplyDemand 'JOB-JOB0002'- Output will be JOB-JOB0001
exec GetSupplyDemand 'JOB-JOB0001'- Output will be ORD00001 - 1.
exec GetSupplyDemand 'ORD00001 - 1'- Output will be NULL
So program will stop here and give Top Level Demand as 'ORD00001 - 1'

Can you post the code for the GetSuppyDemand stored procedure?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)