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:30:36

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources