August 19, 2009 at 8:53 am
I have limited experiance creating stored procedures, so I need some help if possible. I need to use the results of a select statement and run several logic checks on it to determine if I need to run an insert statement. If possible I would like to put this all in one procedure so I can run a dts package on it. Any help would be appreciated.
August 19, 2009 at 8:59 am
what sort of 'logic check' do you need to do ?
August 19, 2009 at 9:03 am
You really need to provide more details and explain what you are trying to do. Include some sample data that shows what checks you want to do.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
August 19, 2009 at 1:05 pm
SELECT PartLocation, PartNumber, CurrentReorderAmount, AmounttoReorder, CurrentInventory, AmountInDistributionCenter
FROM InventoryTable
where (COALESCE (AmountInDistributionCenter, 0)>0)
If Not SQL End of File then
If AmountInDistributionCenter>0 Then
j=0
TransferAmount=0
If AmountInDistributionCenter>=AmounttoReorder Then
TransferAmount=AmounttoReorder
Else
TransferAmount=AmountInDistributionCenter
End If
Insert INTO TransferTable
Select Max of TransferTable Pkey
If SQL Not EoF Then
Insert INTO TransferHistoryTable
End If
End If
End If
Basically something like this. The point of the sub procedure is to grab a list of parts that need to be reordered that are in our distribution center and if there is enough quantity to create a transfer for them then adjust inventory to reflect the the change.
August 19, 2009 at 2:53 pm
What you probably want to do is combine this logic into your select. As you look to insert data, add the "logic" check into the WHERE clause.
insert TransferTable
select Product, AmountToReorder
from Inventory i
inner join DistributionCenter d
on i.product = d.product
where i.inventory = 0
and d.AmountInDistributionCenter > 0
and i.AmountToRedorder <= d.AmountInDistributionCenter
There is no such thing as SQL End of File. You want to think about working with all rows at the same time.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
August 19, 2009 at 3:44 pm
It seems a cursor (even a While loop) will help you code the logic you need.
Swarndeep
http://talksql.blogspot.com
August 19, 2009 at 3:45 pm
right ... the OP is thinking like a C++ programmer who iterates through files and not an SQL programmer who works with sets of data.
the logic could be put into a few successive selects/updates.
August 20, 2009 at 12:09 pm
Ya i'm actually an asp programmer. What i'm trying to do is go from using an asp page to run this set to try to do it in SQL Stored Procedure. That way I can run it on a DTS package instead of waiting for someone to hit the page or multiple people hitting the page and causing duplication.
August 20, 2009 at 12:17 pm
Also I have other insert statements that use the information from the first sql statement, how would i pull that data to put it into an insert?
August 20, 2009 at 12:43 pm
You'd have to provide more information about what you mean. If you can show a few tables and some sample data, showing how it's inserted, we can help you.
The thing to do is try and write some of this. We're happy to help, but this isn't consulting where we do all the work for you. Write some queries (SELECT) and see the data returned. Once you have an idea of getting the data for one product, you can remove a WHERE clause that limits it to one product and handle all of them at once.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
August 20, 2009 at 2:30 pm
Sorry i'm not trying to have you guys do everything for me. I have never really don't a procedure before and i'm not sure if i can accomplish what i want using one. As far as doin the work, I have the code done is asp for this i'm trying to get around having to load the page every time to run the code. If I can use the record information from the SQL statement then run some logic against I think I can accomplish what i'm looking to do. I just don't know how to pull information from the sql return and the syntax for the logic.
Here is more examples:
SELECT PartLocation, PartNumber, CurrentReorderAmount, AmounttoReorder, CurrentInventory, AmountInDistributionCenter, CurrentInventoryLocation
FROM InventoryTable
where (COALESCE (AmountInDistributionCenter, 0)>=AmounttoReorder
Insert into TransferTable PartNumber, PartLocation, Qty, DistribLocation, CurrentInventoryLocation
Select Max(Transferpkey) from TransferTable
Insert Max(TransferPkey) Into TransferHistoryTable
SELECT PartNumber, InventQuantities FROM InventQuantities WHERE (PartNumber=InventoryTable.PartNumber) and InventLocation=DistributionCenter
If it doesn't exist in InventQuantities Then
insert the transfer record information into InventQuanities.
Else
Update InventQuantities with part tranfer information
End If
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply