Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > STORED PROCEDURE HELP

Reply
Thread Tools

STORED PROCEDURE HELP

 
 
Simon Gare
Guest
Posts: n/a
 
      05-09-2007
Hi need a stored procedure to replace the 4 commands listed below

UPDATE dbo.booking_form SET total_cost = mileage_charge + waiting_charge +
CarParkToDriver
UPDATE dbo.booking_form SET VAT = total_cost * 17.5/100
UPDATE dbo.booking_form SET GrandTotal = total_cost + VAT
UPDATE dbo.booking_form SET TotalToDriver = MileageToDriver +
WaitingToDriver + CarParkToDriver

all of them have the same WHERE clause which I need a little assistance with
the date issue

WHERE allocated = COMPLETED AND TimeOfBooking BETWEEN '" + getdate(), "'",
"''" + " 00:00:00' AND '" + getdate(), "'", "''" + " 23:59:59'

also how to call it?

Thanks

--
Simon Gare
The Gare Group Limited

website: www.thegaregroup.co.uk
website: www.privatehiresolutions.co.uk


 
Reply With Quote
 
 
 
 
Bob Lehmann
Guest
Posts: n/a
 
      05-09-2007
Why are you storing calculated values in the table?

Bob Lehmann

"Simon Gare" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Hi need a stored procedure to replace the 4 commands listed below
>
> UPDATE dbo.booking_form SET total_cost = mileage_charge + waiting_charge

+
> CarParkToDriver
> UPDATE dbo.booking_form SET VAT = total_cost * 17.5/100
> UPDATE dbo.booking_form SET GrandTotal = total_cost + VAT
> UPDATE dbo.booking_form SET TotalToDriver = MileageToDriver +
> WaitingToDriver + CarParkToDriver
>
> all of them have the same WHERE clause which I need a little assistance

with
> the date issue
>
> WHERE allocated = COMPLETED AND TimeOfBooking BETWEEN '" + getdate(), "'",
> "''" + " 00:00:00' AND '" + getdate(), "'", "''" + " 23:59:59'
>
> also how to call it?
>
> Thanks
>
> --
> Simon Gare
> The Gare Group Limited
>
> website: www.thegaregroup.co.uk
> website: www.privatehiresolutions.co.uk
>
>



 
Reply With Quote
 
 
 
 
Simon Gare
Guest
Posts: n/a
 
      05-09-2007
Because I cant think of anyway else to do it Bob, unless you can suggest
something?

Simon


 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      05-10-2007
Simon Gare wrote:
> Hi need a stored procedure to replace the 4 commands listed below
>
> UPDATE dbo.booking_form SET total_cost = mileage_charge +
> waiting_charge + CarParkToDriver
> UPDATE dbo.booking_form SET VAT = total_cost * 17.5/100
> UPDATE dbo.booking_form SET GrandTotal = total_cost + VAT
> UPDATE dbo.booking_form SET TotalToDriver = MileageToDriver +
> WaitingToDriver + CarParkToDriver
>
> all of them have the same WHERE clause which I need a little
> assistance with the date issue
>
> WHERE allocated = COMPLETED AND TimeOfBooking BETWEEN '" + getdate(),
> "'", "''" + " 00:00:00' AND '" + getdate(), "'", "''" + " 23:59:59'
>
> also how to call it?
>

I'm with Bob. Unless these values need to reflect history, they should not
be stored in the table. Create a view:

CREATE VIEW vBooking_form_calculations AS
SELECT
<the primary key column(s) of booking_form>,
mileage_charge + waiting_charge + CarParkToDriver as total_cost ,
(mileage_charge + waiting_charge + CarParkToDriver)
* 17.5/100 as VAT,
mileage_charge + waiting_charge + CarParkToDriver
+ (mileage_charge + waiting_charge + CarParkToDriver)
* 17.5/100 as GrandTotal ,
MileageToDriver + WaitingToDriver + CarParkToDriver as
TotalToDriver
FROM dbo.booking_form

Now, whenever you need those values, just create a join between this view
and booking_form

Alternatively, if using SQL2000 or above, you can create calculated columns
_in your table_.

For your WHERE clause, you should use:

WHERE allocated = COMPLETED AND TimeOfBooking >= getdate()
AND TimeOfBooking < DATEADD(day,1,getdate())



--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


 
Reply With Quote
 
Simon Gare
Guest
Posts: n/a
 
      05-10-2007
Thanks Bob will try that out.

Regards
Simon


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
'Procedure or function <stored procedure name> has too many arguments specified',,,ARGH! Mike P ASP .Net 0 06-19-2006 01:19 PM
help! jdbtable call stored procedure rob Java 0 09-21-2005 03:37 AM
Re: need help with a complicated stored procedure please iixv Microsoft Certification 0 07-30-2005 04:34 PM
Help with Code to Execute a Stored Procedure Jeff Thur ASP .Net 2 02-07-2005 08:58 PM
error msg help needed on stored procedure TJS ASP .Net 4 11-12-2003 11:15 PM



Advertisments