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

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!

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.

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

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.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources