Help with a Stored Procedure

Question

Post reply

Help with a Stored Procedure

Mitchel

SSC Veteran

Points: 298
More actions
February 15, 2006 at 9:45 am

#93384

Hi,
I currently have a report (ASP/VB SCRIPT) that queries our SQL 2000 DB and brings back a distinct list of classes which were taught and also the number of times it was taught per quarter. I am currently using multiple recordsets and the processing is done on the page so you can image that a report that has to go through over 5000 records can take a while because of all of the "roundtrips" to the SQL Server.
What I am currently doing is using one recordset to get all of the distinct class codes and setting up repeat region, then I pass that value to four different recordsets sets (winter, spring, summer, fall) and they count the times the class was taught as a nested repeat region/loop, then it goes to the next class and performs the same counting function.
I know this is not very efficient but I cannot figure out how to code a stored procedure to do this.
Attached is an image that will help illustrate what I am trying to do.
Thanks for taking a look,
Mitch

Viewing 8 posts - 1 through 7 (of 7 total)

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

Jo Pattyn SSC-Dedicated Points: 32350 More actions · Answer 1

Hmm, the image doesn't show up.

Can you post your report sql + table definitions & some sample data?

*calling a stored procedure from asp

http://support.microsoft.com/kb/q164485/

*creating a stored procedure

CREATE PRODECURE dbo.USP_MY_PROCEDURE

(@Param1 as int

,@Param2 as varchar(4000)

...

)

AS

SET NOCOUNT ON /*skip a roundtrip*/

/*insert sql code below*/

Mitchel SSC Veteran Points: 298 More actions · Answer 2

Hi Jo,

Here is a link to the image, I think it will help explain what I am trying to do:

http://www.fotash.com/new1/sto_sample.gif

Thanks!

Mitch

Jo Pattyn SSC-Dedicated Points: 32350 More actions · Answer 3

CREATE STORED PROCEDURE dbo.USP_CLASSTRACK_PIVOT_QUARTER

AS

SET NOCOUNT ON

SELECT classcode,

SUM(CASE Quarter WHEN 'winter' THEN Amount ELSE 0 END) AS 'Count Winter',

SUM(CASE Quarter WHEN 'spring' THEN Amount ELSE 0 END) AS 'Count Spring',

SUM(CASE Quarter WHEN 'summer' THEN Amount ELSE 0 END) AS 'Count Summer',

SUM(CASE Quarter WHEN 'fall' THEN Amount ELSE 0 END) AS 'Count Fall'

FROM dbo.classtrack

GROUP BY classcode

order by classcode

should give what you want.

Have a look at pivot tables in the book online.

Mitchel SSC Veteran Points: 298 More actions · Answer 4

Jo,

Thank you for the fabulous help!!!

I was able to use the info (pivot tables) and example you gave me and the report which used to take 40 seconds to run and generate an Excel file is now taking 4 seconds!!!

Once again...thanks!

Mitch

Jo Pattyn SSC-Dedicated Points: 32350 More actions · Answer 5

You're welcome 🙂

Keep in mind that set-based solutions are likely faster than record by record based solutions in sql

Mitchel SSC Veteran Points: 298 More actions · Answer 6

"set-based solutions"....Can you explain what you mean?

Mitch

Jo Pattyn SSC-Dedicated Points: 32350 More actions · Answer 7

Instead of reading a file byte per byte (1 I/O per byte) , a program can read them in chunks of y bytes (1 I/O command for y bytes in total)

The same applies how sql server can access/transform the data it has to manipulate.

Instead of hauling a ton of water with bucket, you order a van to move it.