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)
 Need help with cursor.

Author  Topic 

hardtech
Starting Member

3 Posts

Posted - 2014-10-03 : 17:14:29
Hello all,

Was hoping to get a little help on this project I have been tasked to do. Its been 10+ years since I even messed with a cursor but for the life of me this one has got me. First set of code is the HTML mock up of what this will be spitting out into someones email. Second code is the SP as it sits now. Currently it seems to do an endless loop and not creating the html. This is on a 2000 machine that will be moved to 2012 in a couple of months. Feel free to mock or show me how horrible I am. Long as I end up figuring this one out.

HTML
<html>
<body>
<br />
<br />
<font face="verdana" size="2">The board has just been updated with the following values:</font>
<br />
<br />
<font face="verdana" size="2"><b>Campus A</b></font>
<table>
<tr>
<td>8-2014</td>
<td>Metric A:</td>
<td>1</td>
</tr>
<tr>
<td>8-2014</td>
<td>Metric B:</td>
<td>5</td>
</tr>
<tr>
<td>8-2014</td>
<td>Metric C:</td>
<td>3</td>
</tr>
</table>
<br />
<br />
<font face="verdana" size="2"><b>Campus B</b></font>
<table>
<tr>
<td>8-2014</td>
<td>Metric A:</td>
<td>3</td>
</tr>
<tr>
<td>8-2014</td>
<td>Metric B:</td>
<td>2</td>
</tr>
<tr>
<td>8-2014</td>
<td>Metric C:</td>
<td>4</td>
</tr>
</table>
<br />
<br />
<font face="verdana" size="2">
<a href="https://www.website.net/login.asp">Click here to login.</a>
<br />
<br />Please contact the us should you have any questions.
<br />
<br />Phone: 555-555-5555   Email:
<a href="mailto:help@website.net">help@website.net</a>
<br />
<br />Thank you.</font>
</body>
</html>


TSQL
BEGIN
-- eMail Variables --
DECLARE @email_subject VARCHAR(1000),
@email_header VARCHAR(8000),
@email_body VARCHAR(8000),
@email_footer VARCHAR(8000),
@email_complete VARCHAR(8000),
@vTotalProviderCount INT,
@vTotalCampusCount INT,
@vTotalMetricCount INT,
@vProviderCount INT,
@vCampusCount INT,
@vProviderNum VARCHAR(20),
@vProviderName VARCHAR(80),
@vCampus VARCHAR(500),
@vMetricCount INT,
@intMessageID INT,
@vDate_Label VARCHAR(10),
@vMetricName VARCHAR(60),
@vMetricValue VARCHAR(60)

-- Select * From test_email
SELECT @email_subject = Substring(Db_name(), 5, Len(Db_name()) - 4)

SET @email_header = '<html>
<body>
<br />
<br />
<font face="verdana" size="2">The dashboard has just been updated with the following values for your metrics:</font>
<br />
<br />'
SET @email_header = '<br />
<br />
<font face="verdana" size="2">
<a href="https://www.website.net/login.asp">Click here to login.</a>
<br />
<br />Please contact the PSR support desk should you have any questions.
<br />
<br />Phone: 555-555-5555   Email:
<a href="mailto:help@website.net">help@website.net</a>
<br />
<br />Thank you.</font>
</body>
</html>'
SET @vTotalProviderCount = (
SELECT count(DISTINCT provider_num)
FROM test_email -- This need to change to temp table.
)

DECLARE cur_provider CURSOR
FOR
SELECT DISTINCT provider_num
FROM test_email -- This need to change to temp table.

OPEN cur_provider

SET @vProviderCount = 0

WHILE @vProviderCount < @vTotalProviderCount
BEGIN
FETCH NEXT
FROM cur_provider
INTO @vProviderNum

------------------------------------
SET @vTotalCampusCount = (
SELECT count(DISTINCT campus)
FROM test_email -- This need to change to temp table.
WHERE provider_num = @vProviderNum
)

DECLARE cur_campus CURSOR
FOR
SELECT DISTINCT campus
FROM test_email -- This need to change to temp table.
WHERE provider_num = @vProviderNum

OPEN cur_campus

SET @vCampusCount = 0

WHILE @vCampusCount < @vTotalCampusCount
BEGIN
FETCH NEXT
FROM cur_campus
INTO @vCampus

-------------------------------
--set = N'<font face="verdana" size="2"><b>'+ltrim(rtrim(@vCampus))+'</b></font>'
SET @vTotalMetricCount = (
SELECT count(*)
FROM test_email -- This need to change to temp table.
WHERE provider_num = @vProviderNum
AND campus = @vCampus
)

DECLARE cur_metric_CT CURSOR
FOR
SELECT date_label,
dial_label,
metricvalue,
provider_num,
last_name + ', ' + first_name
FROM test_email -- This need to change to temp table.
WHERE provider_num = @vProviderNum
AND campus = @vCampus

OPEN cur_metric_CT

SET @vMetricCount = 0
SET @email_body = N'<font face="verdana" size="2"><b>' + ltrim(rtrim(@vCampus)) + '</b></font>'

FETCH NEXT
FROM cur_metric_CT
INTO @vDate_Label,
@vMetricName,
@vMetricValue,
@vProviderNum,
@vProviderName

WHILE @vMetricCount < @vTotalMetricCount /*loop for Metrics*/
BEGIN
SET @email_body = @email_body + N'<table><tr>'
SET @email_body = @email_body + N'<td>' + @vDate_Label + '<td>'
SET @email_body = @email_body + N'<td>' + @vMetricName + '<td>'
SET @email_body = @email_body + N'<td>' + @vMetricValue + '<td>'
SET @email_body = @email_body + N'</tr></table>'

FETCH NEXT
FROM cur_metric_CT
INTO @vDate_Label,
@vMetricName,
@vMetricValue,
@vProviderNum

SET @vMetricCount = @vMetricCount + 1
END

SET @email_complete = @email_header + @email_body + @email_footer

CLOSE cur_metrics_CT

DEALLOCATE cur_metrics_CT

END

CLOSE cur_provider

DEALLOCATE cur_provider
END
END

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-04 : 08:54:12
Why are you using cursors? Avoid them at all costs!
Go to Top of Page

hardtech
Starting Member

3 Posts

Posted - 2014-10-06 : 08:30:25
quote:
Originally posted by gbritton

Why are you using cursors? Avoid them at all costs!



If you got a better way, I am all ears. Only way I could think of to create the emails.
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-06 : 09:53:08
The only thing I see happening is in your innermost loop:

SET @email_body = @email_body + N'<table><tr>'
SET @email_body = @email_body + N'<td>' + @vDate_Label + '<td>'
SET @email_body = @email_body + N'<td>' + @vMetricName + '<td>'
SET @email_body = @email_body + N'<td>' + @vMetricValue + '<td>'
SET @email_body = @email_body + N'</tr></table>'
...
SET @email_complete = @email_header + @email_body + @email_footer



You can easily rewrite this as a single query supported by CTEs. You would only need a cursor if you had a call to another Stored Procedure somewhere, which you don't

Go to Top of Page

hardtech
Starting Member

3 Posts

Posted - 2014-11-06 : 15:59:18
Guess im lost.

Could you help me with this or maybe a small demo. Whats getting me I guess is I need it to generate the body info and have it broken down by Campus then Metric within that campus.

I look forward to any help, ive just been splitting my head on this one trying to get it to work. Im horrible when it comes to cursor's and never use them. All the examples that surround me at work are cursor related to send the email so maybe that's why im not seeing your vision.
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-06 : 16:21:01
Start on the inside and work your way out. e.g. here's the meat of your inner cursor without the cursor:


declare @vCampus varchar(20) = 'Harvard';
declare @t table (date_label varchar(20), dial_label varchar(20), metricvalue varchar(20))

insert into @t (date_label, dial_label, metricvalue) values
('date1', 'dial1', 'metric1'),
('date2', 'dial2', 'metric2')

select x.x.value('.', 'nvarchar(max)')
from (
SELECT N'<font face="verdana" size="2"><b>' + ltrim(rtrim(@vCampus)) + '</b></font>'
+ N'<table><tr>'
+ N'<td>' + date_label + '<td>'
+ N'<td>' + dial_label + '<td>'
+ N'<td>' + metricvalue + '<td>'
+ N'</tr></table>' + char(10) + char(13)
from @t for xml path(''), type
) x(x)


Note that we need to take xml as a type (to encapsulate the embedded tags) then out again (using value) to get the form you need.

This is just an example to get you started. Once you've got the innermost section working, work your way out
Go to Top of Page
   

- Advertisement -