Wrox Programmer Forums
|
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 8th, 2008, 08:11 AM
Authorized User
 
Join Date: Mar 2008
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default Stored Procedures help

Hey,

I need to write two stored procedures and link them to a button on a web form. Basically I have a web form called OrderAllocation.aspx This form takes orders and allocates them to ships (Orders and Ships being two different tables). This "allocation" involves adding the select order and ship to a new table called OrdersonShip. I need to write the two stored procedures so that 1) I can get that the Ship has space to take the order and that the ship isn't full already. 2) If the ship is able to take the order insert a new line in the OrdersonShip table (consisting of the ShipID and OrderID) and then add the value of the order (NumofItems) to the currentnumofCargo of the ship in the Ship table. To me it sounds horribly complicated, but I have no idea how to start. I have looked at a few guides online but they don not seem to help, or they don't go as indepth as I need them to.

Below is a link which shows my database diagram.


http://img396.imageshack.us/img396/8...diagramtn6.jpg

Many thanks for any help, it's driving me mad working our where I should start and what to do to complete this task.

Peter


 
Old April 8th, 2008, 06:36 PM
Wrox Author
 
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

Not sure how this equates to two sprocs. Without knowning any of the business rules of your database, I assume, something like this would work:

CREATE PROCEDURE usp_ShipOrder
(
   --Any Params
)
AS

if (SELECT Sum(NumberofCargo - CurrentNumOfCargo) From Ship where ShipID = value) > 0
Begin
    --Space is available on the ship, add the record
End
Else
Begin
    --Space is not available on the ship, exit
End

Does that make sense?

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
.: Wrox Technical Editor / Author :.
Wrox Books 24 x 7
================================================== =========
 
Old April 12th, 2008, 01:31 AM
Friend of Wrox
 
Join Date: Aug 2004
Posts: 385
Thanks: 0
Thanked 0 Times in 0 Posts
Default

you need to give more info if you want more help......






Similar Threads
Thread Thread Starter Forum Replies Last Post
Stored Procedures KeviJay VB Databases Basics 1 June 5th, 2008 07:17 AM
stored procedures MunishBhatia SQL Server 2005 4 April 12th, 2008 01:39 AM
STORED PROCEDURES shazia1 SQL Server ASP 7 September 26th, 2007 06:11 AM
Stored Procedures itHighway SQL Server 2000 3 November 23rd, 2005 10:08 AM
Stored Procedures stu9820 Access 3 February 8th, 2004 01:13 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.