Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language 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 December 8th, 2006, 12:54 PM
Authorized User
 
Join Date: Jun 2004
Posts: 25
Thanks: 0
Thanked 0 Times in 0 Posts
Default Stored Procedure loop ?

Hi,

I have an online ASP application that is using a stored procedure containing many queries. I need to add a loop query to the stored procedure for an additional query.

In my SQL database I have a table (ecastdata) containing a set of 30 similar fields (ClickThru1 to ClickThru30) for example:-

ClickThru1
ClickThru2
ClickThru3
blah blah etc.
blah blah etc.
ClickThru30

I need to have the same query looped for each of the 30 fields so I dont manually repeat the query 30 times!

The query I need for each field :-

Code:
SELECT SUM{ClickThru#} AS intClicks# FROM ecastdata WHERE EcastID = @strEcastID
BTW (# is the number for each field)
@strEcastID will be a fixed value.


How can I have above query in a loop so I dont have to repeat the query for each of the 30 fields ??

Please help

 
Old December 13th, 2006, 08:51 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 596
Thanks: 1
Thanked 3 Times in 3 Posts
Default

This is a strange relationship.
Normally when you have 30 odd fields relating to a particular field you would have them in a related table on a seperate row each, this would make your query very straght forward with a Group By clause.
In this sutuation a SUM will not work as you intended because it is meant to sum relational data.(Experts please confer)
In your situation your query should look something like below.

Code:
Select (ClickThru1 + ClickThru2 + etc ) FROM ecastdata WHERE EcastID = @strEcastID
If the number of fields, ie 30, is set in stone then this approach is fine, if you may one day need 31+ fields, try a related table asap

======================================
They say, best men are molded out of faults,
And, for the most, become much more the better
For being a little bad.
======================================
 
Old December 14th, 2006, 06:06 AM
Authorized User
 
Join Date: Jun 2004
Posts: 25
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks Rod, however I need the SUM for "each" ClickThru field, not for all the ClickThru fields combined together. I really don't want to have to write out the query 30+ times for the 30 fields.

Please advise

 
Old December 14th, 2006, 08:45 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 596
Thanks: 1
Thanked 3 Times in 3 Posts
Default

Hmmmm, ok, so there are many records with the same value for @strEcastID and you want to sum the clickThru fields in those records.

Select SUM(ClickThu1) as intClickThru1, SUM(ClikThru2)as intClickThru2, SUM(ClickThu3) as intClickThru1[3, ....... from ecastdata where EcastID = @strEcast.

Let me know if Im still off track, this seems pretty straight forward, can you post the table schema and a sample of what you would like the resulting recordset(s) to look like.

======================================
They say, best men are molded out of faults,
And, for the most, become much more the better
For being a little bad.
======================================
 
Old December 20th, 2006, 06:32 AM
Authorized User
 
Join Date: Jun 2004
Posts: 25
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks Rod. I will try what you have suggested.

 
Old December 21st, 2006, 05:51 AM
Registered User
 
Join Date: Dec 2006
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Form a dynamic query based on the syscolumns for your table. Then execute the dyn. query...you will get what you want...You need not change the query in future if additional fields are added






Similar Threads
Thread Thread Starter Forum Replies Last Post
Loop Stored Procedure corrineo SQL Server 2005 1 November 29th, 2007 10:48 AM
stored procedure keyvanjan Classic ASP Basics 6 August 1st, 2006 07:42 AM
stored procedure lokey VB How-To 7 June 30th, 2005 12:37 AM
stored procedure kvanchi ADO.NET 1 December 9th, 2004 07:27 AM
Stored Procedure bmains SQL Server ASP 2 October 8th, 2004 03:19 AM





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