stored proc

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















































    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">
    <!--
    body,td,th {
    font-family: Verdana;
    color:#7d6f62;
    }
    body {background-color:#f5f4f1;
    }
    -->
    </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














  • 10 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,

     

  • 10 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">
    <!--
    body,td,th {
    font-family: Verdana;
    color:#7d6f62;
    }
    body {background-color:#f5f4f1;
    }
    -->
    </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='tim@dip.com'

    -- 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 ='tim@dip.com',
    @Subject =@MailSubject,
    @Body =@MailBody




    end
    GO















































     

  • 10 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

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“To iterate is human, to recurse divine” - L. Peter Deutsch