stored proc

neoandzuco · #1 (**permalink**) Feb 13th, 2007, 14:56

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

neoandzuco · #2 (**permalink**) Feb 14th, 2007, 14:39

Anybody?????

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Email stored in db	tazek0	Classic ASP	1	Mar 3rd, 2006 12:20
Dynamic Stored Proc	kebi	Databases	0	Nov 2nd, 2005 05:25
Stored proc	kebi	Databases	0	Oct 28th, 2005 11:17
ASP ADO Connection to return XML from SQL Proc	NickyJ	Classic ASP	2	Aug 11th, 2005 11:39
ASP and DB2 Stored Procedures	cruza	Classic ASP	4	Dec 1st, 2003 17:24

		Webforumz.com > Main Forums > Program Your Website > Databases
stored proc