.NET
Java
Open Source
Mobile
Database
- SQL
- Microsoft Access
- MySQL
- Oracle
- SQL Server
Architecture
RIA & Web
Toolbox

stored proc

Last post was 20 Feb 2007 at 04:02

sql server Newcastle upon Tyne, United Kingdom

- Reply
14 years ago

by neoandzuco

Tim

, United Kingdom

Joined 14 years ago
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='

[email protected]'

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='

[email protected]'

-- 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 ='

[email protected]',
@Subject =@MailSubject,
@Body =@MailBody

end
end
GO

Any help with this would be greatly appreciated.

Thanks

Tim

Report abuse
- Reply
14 years ago

by Kassem

Kassem Baalbaki

Riyadh, Saudi Arabia

Joined 14 years ago
Hi,

I don't know what are you doing or what you need the curser for so I will be helping you through the SQL only. The above table result can be generated using grouping:

CREATE PROCEDURE example

select project, sum(points) as totalpoints
from projects
group by project

Hope this works for you,

Report abuse
- Reply
14 years ago

by neoandzuco

Tim

, United Kingdom

Joined 14 years ago
Hi,

Thanks for the reply - I've since tried using that method - and in query analyser it works fine and outputs the results I need - but Im having trouble using this query to output the results table in the stored proc as I need to send it as a html email -

I thought this might work but it doesnt, do you know how I can use this query in an sp and send the results via cdosysmail ?

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 rewards</b></td></tr>'

SELECT '<tr><td>' + projectname + '</td><td>' + sum(pointsrewards)+ '</td></tr>' from usersurveyhistory group by projectname

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

SET @MailTo='[email protected]'

-- get next record

FETCH NEXT FROM c1 INTO @proname END

-- close and shut down the cursor

CLOSE c1 DEALLOCATE c1

exec dbo.sp_send_cdosysmail

@From = @MailFrom,
@To =@MailTo,
@bcc ='[email protected]',
@Subject =@MailSubject,
@Body =@MailBody

end
GO

Report abuse
- Reply
14 years ago

by reivax

Xavier Oliquiano

, Philippines

Joined 15 years ago
I don't know if I understand it correctly, I think you can just use a select statement and group the project na

Report abuse