Stored procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Stored procedure

Author

Topic

neoandzuco
Starting Member

5 Posts

Posted - 2007-02-13 : 07:27:51

Hi there,

Im struggling in how to proceed with what I need - which is basically this;

I have a table of project names and the number of points associated with each project.
each project is listed more than once in the table.

I need to create a stored procedure that will provide me with an outputted table which I can send as a report with the total amount of points for each project.

ie
project points
ab1 100
ab1 200
ab1 100
ab2 200
ab2 300 etc

outputted table

project totalpoints
ab1 400
ab2 500

I have an understanding of cursors etc but im dont know how to add up the points for individual projects which are list numerous times.

The code I have so far is this but I fairly sure im no where near - this basically just lists
everything in the table for the last month.

CREATE PROCEDURE [dbo].[points count test]

AS

BEGIN

DECLARE @NumRows int

DECLARE @MailSubject nvarchar(100) -- subject of the e-mail sent to the specified users

DECLARE @MailBody varchar(4000) -- body of the e-mail sent to the specified users

DECLARE @MailFrom nvarchar(50) -- from string of the e-mail sent to the specified users

DECLARE @MailTo nvarchar(50) -- from string of the e-mail sent to the specified users

DECLARE @proname nvarchar(200) -- for use with the cursor

DECLARE @email nvarchar(200) --for use with the cursor

SET @MailFrom='Panel@History.com'

SET @MailSubject='online survey count'

SET @NumRows = 2000

BEGIN

-- set the initial state of the body of the e-mail

SET @MailBody='<html>
<head>
<style type="text/css">

</style></head>
<body><p><b>Survey start count</b></p><table border="1" cellpadding"0" cellspacing"0">
<tr><td><b>Project name</b></td><td><b>Points awardedt</b></td></tr>'

-- define the body of the e-mail

DECLARE c1 CURSOR FOR SELECT '<tr><td>' + projectname+ '</td><td>' + pointsawarded + '</td></tr>' from usersurveyhistory where date BETWEEN DATEADD(m, -2, GetDate()) AND GetDate()-1

-- open the cursor

OPEN c1

-- get the first record

FETCH NEXT FROM c1 INTO @proname

-- while we have records to process

WHILE @@FETCH_STATUS = 0 BEGIN

-- add the new records to the Mail Body

SET @MailBody=@MailBody + @proname

SET @MailTo='tim@history.com'

-- get next record

FETCH NEXT FROM c1 INTO @proname END

-- close and shut down the cursor

CLOSE c1 DEALLOCATE c1

SET @MailBody=@MailBody + '</table><br><hr size="1"></body></html>'

exec dbo.sp_send_cdosysmail

@From = @MailFrom,
@To =@MailTo,
@bcc ='tim@history.com',
@Subject =@MailSubject,
@Body =@MailBody

end
end
GO

Any help with this would be greatly appreciated.

Thanks

Tim

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-13 : 07:37:36

duplicate thread http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79067

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2007-02-13 : 07:44:28

Use this one, I've locked the other.

Damian
"A foolish consistency is the hobgoblin of little minds." - Emerson

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-13 : 07:46:58

no cursor required


select project, total_points = sum(points)
from   yourtable
group by project

quote:
I have an understanding of cursors

Maybe should learn about set based before learning about cursors

neoandzuco
Starting Member

5 Posts

Posted - 2007-02-13 : 07:48:59

quote:
Originally posted by khtan

duplicate thread http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79067

KH

I know, its my duplicate - my pc was doing funny things when I posted - any help on the subject at hand would be a bigger help.

Thanks

Tim

neoandzuco
Starting Member

5 Posts

Posted - 2007-02-13 : 07:50:50

quote:
Originally posted by khtan

no cursor required

select project, total_points = sum(points)
from yourtable
group by project

quote:
I have an understanding of cursors

Maybe should learn about set based before learning about cursors

KH

Thanks khtan

I appreciate your help :-)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-13 : 07:51:27

quote:
Originally posted by neoandzuco

quote:
Originally posted by khtan

duplicate thread http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79067

KH

I know, its my duplicate - my pc was doing funny things when I posted - any help on the subject at hand would be a bigger help.

Thanks

Tim

Actually you can delete the thread provided that nobody had posted to it yet and also the moderator has not locked it yet

neoandzuco
Starting Member

5 Posts

Posted - 2007-02-13 : 08:31:33

quote:
Originally posted by khtan

no cursor required

select project, total_points = sum(points)
from yourtable
group by project

KH

Hi - that works a treat in sql analyser, How could I incorporate the result set into a html email sent from the stored proc?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources