HTML Code in Stored proc - 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

General SQL Server Forums

New to SQL Server Programming

HTML Code in Stored proc

Author

Topic

aakcse
Aged Yak Warrior

570 Posts

Posted - 2008-06-27 : 06:23:02

Hi all,

Kindly help me in generating html code in Stored proc output will be

one row and one col

containg sql query results set in html code.

some thing like the below o/p should be embeded in html tags.

Col1 Col2 Col3

------------------------------

val1 val2 val3

valx valy valz

how this can be done?

thanks

aak.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-06-27 : 06:33:00

Have a look at this procedure:

ALTER PROCEDURE [dbo].[proc_replication_latency_mailer]
  @EmailRecipients varchar(max), --> Semicolon-separated list of recipients
  @AcceptedLatency int = 15
AS

SET NOCOUNT ON

BEGIN

DECLARE
  @EmailSubject varchar(300),
  @EmailBody varchar(max)

IF EXISTS (SELECT 1 FROM dbo.replication_latency WHERE IsChecked IS NULL AND COALESCE(OverallLatency, 1000) > @AcceptedLatency)
  BEGIN

    SET @EmailSubject = 'Replication latency report'
    SET @EmailBody = '
      <HTML>
      <HEAD>
      <TITLE>' + @EmailSubject + '</TITLE>
      <STYLE TYPE="text/css">
        table { border: 1px solid #666666; border-collapse: collapse; }
        th    { border: 1px solid #666666; font-family: verdana; font-size: 10px; background-color: #CCCCCC; font-weight: bold; vertical-align: top; padding: 2px; }
        td    { border: 1px solid #666666; font-family: verdana; font-size: 10px; vertical-align: top; padding: 2px; }
        p   { font-family: verdana; font-size: 10px; padding: 2px; }
      </STYLE>
      </HEAD>

      <BODY>'

    SET @EmailBody = @EmailBody + '
      <P>The following publications have latency above the current threshold of ' + CAST(@AcceptedLatency AS varchar(3)) + ' seconds:</P>
      <TABLE WIDTH=100%>
      <TR>
      <TH>Publication database</TH>
      <TH>Publication name</TH>
      <TH>Subscriber</TH>
      <TH>Subscriber DB</TH>
      <TH>Overall Latency</TH>
      <TH>Tracer token posted</TH>
      '

    SELECT @EmailBody = @EmailBody + '
      <TR>
      <TD>' + PublicationDatabase + '</TD>
      <TD>' + PublicationName + '</TD>
      <TD>' + Subscriber + '</TD>
      <TD>' + SubscriberDB + '</TD>
      <TD>' + CASE WHEN OverallLatency IS NULL THEN '> 2 mins (timed out)' ELSE CAST(OverallLatency AS varchar(3)) END + '</TD>
      <TD>' + CONVERT(VARCHAR(50), TracerTokenPostTime, 113) + '</TD>'
    FROM dbo.replication_latency
    WHERE IsChecked IS NULL
      AND COALESCE(OverallLatency, 1000) > @AcceptedLatency

    SELECT @EmailBody = @EmailBody + '
      </TABLE>

      </BODY>
      </HTML>'

    EXEC msdb.dbo.sp_send_dbmail
      @profile_name = 'DBMailProfile',
      @recipients = @EmailRecipients,
      @subject = @EmailSubject,
      @body = @EmailBody,
      @body_format = 'HTML'

  END

  --> Make sure that each replication latency error is only emailed once
  UPDATE dbo.replication_latency SET IsChecked = 1 WHERE IsChecked IS NULL

END

- Lumbago

aakcse
Aged Yak Warrior

570 Posts

Posted - 2008-06-27 : 06:38:49

Thanks Lumbago,

I am looking into it and I will get back to you.

Thanks again
aak.

aakcse
Aged Yak Warrior

570 Posts

Posted - 2008-06-27 : 07:00:54

Well below is my code

SELECT
STE_FacilityName
,STE_RQS_StudioID
,STE_CNT_ContactID_CreatedBy
,STE_CreatedDate
FROM TBL_SiteMaster sm
INNER JOIN LK_RequestingStudio rs
ON sm.STE_RQS_StudioID = rs.RQS_StudioID
INNER JOIN TBL_UserContacts uc
ON sm.STE_CNT_ContactID_CreatedBy = uc.CNT_ContactID

The above code will be in SP which receives a date as an in parameter
say if date is from the 3rd week of the month then it should give the result of previous week i,e 2nd week details ( this I will include in the logic)

and need to send this output as a email to the xyz person.

Thanks again.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-06-27 : 07:08:06

I have given you a solution for formatting the results of a query as an html-email, and it includes adding recipients as well. All you need to do is to change a few minor things to adapt it to your needs. If you need help creating the select you should post a new topic.

- Lumbago

aakcse
Aged Yak Warrior

570 Posts

Posted - 2008-06-27 : 08:00:20

Thank you very much Lumbago,

I will prepare the select statement and will include that in the
html looking into your code.

If there is any thing I will get back to you.
thanks again for your immediate response.

-aak.

aakcse
Aged Yak Warrior

570 Posts

Posted - 2008-06-27 : 09:37:08

Hi Lumbago,

Below is my complete code I am able to compile it
can you have a look once plz I have included my below
commented select stm in to yours code.

and also can you help me in executing this
what else is needed to run this proc.

wht setup is needed to execute this

I am from Oracle plsql b/g this is for the fist engagment I am working on SQL SERVER 2005.

Thank you very mucy Lumbago.

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO



ALTER PROCEDURE [dbo].[TMP_EmailContent06262008_SP] 
    @mydate datetime,
	@EmailRecipients varchar(max) 
	AS

BEGIN

DECLARE
	@EmailSubject varchar(300),
	@EmailBody varchar(max),
	@weekNum INT,	
	@yearNum INT,
	@weekDay datetime
 
	SELECT @weekNum = CAST(DATEPART(WK, @mydate) AS INT) - 1
	
	SELECT @yearNum = CAST(DATEPART(YYYY, @mydate) AS INT)

		BEGIN
			SET @EmailSubject = 'Details of '+@weekNum+'th Week of Year'+ @yearNum  
			SET @EmailBody = '
			<HTML>
			<HEAD>
			<TITLE>' + @EmailSubject + '</TITLE>
			<STYLE TYPE="text/css">			
			table { border: 1px solid #666666; border-collapse: collapse; }
			th    { border: 1px solid #666666; font-family: verdana; font-size: 10px; background-color: #CCCCCC; font-weight: bold;					vertical-align: top; padding: 2px; }
			td    { border: 1px solid #666666; font-family: verdana; font-size: 10px; vertical-align: top; padding: 2px; }
			p   { font-family: verdana; font-size: 10px; padding: 2px; }
			</STYLE>
			</HEAD>
			<BODY>'
			SET @EmailBody = @EmailBody + '
			
			<TABLE WIDTH=100%>
			<TR>
			<TH>STE_FacilityName</TH>
			<TH>STE_RQS_StudioID</TH>
			<TH>STE_CNT_ContactID_CreatedBy</TH>
			<TH>STE_CreatedDate</TH>
			<TH>WEEKNO</TH>
			<TH>WEEKDAY</TH>
			'
			
			SELECT @EmailBody = @EmailBody + 
			'<TR><TD>' + STE_FacilityName +								'</TD> 
				 <TD>' + STE_RQS_StudioID +								'</TD>
				 <TD>' + STE_CNT_ContactID_CreatedBy +					'</TD> 
				 <TD>' + STE_CreatedDate +								'</TD>
				 <TD>' + CAST(DATEPART(wk,STE_CreatedDate) AS INT )+	'</TD> 
				 <TD>' + DATENAME(DW,STE_CreatedDate)+					'</TD>
			'
			FROM TBL_SiteMaster sm 
				INNER JOIN LK_RequestingStudio rs ON sm.STE_RQS_StudioID = rs.RQS_StudioID 
				INNER JOIN TBL_UserContacts uc    ON sm.STE_CNT_ContactID_CreatedBy = uc.CNT_ContactID
			WHERE CAST(DATEPART(wk,STE_CreatedDate) AS INT )= @weekNum --@weekNum
			ORDER BY STE_CreatedDate ASC

			SELECT @EmailBody = @EmailBody + '
			</TABLE>

			</BODY>
			</HTML>'
			EXEC msdb.dbo.sp_send_dbmail
			@profile_name = 'DBMailProfile',
			@recipients = @EmailRecipients,
			@subject = @EmailSubject,
			@body = @EmailBody,
			@body_format = 'HTML'

		END
END


/*
			SELECT 
				 STE_FacilityName
				,STE_RQS_StudioID
				,STE_CNT_ContactID_CreatedBy
				,STE_CreatedDate
				,CAST(DATEPART(wk,STE_CreatedDate) AS INT ) AS WEEKNO
				,DATENAME(DW,STE_CreatedDate) AS WEEKDAY
			FROM TBL_SiteMaster sm 
				INNER JOIN LK_RequestingStudio rs ON sm.STE_RQS_StudioID = rs.RQS_StudioID 
				INNER JOIN TBL_UserContacts uc    ON sm.STE_CNT_ContactID_CreatedBy = uc.CNT_ContactID
			WHERE CAST(DATEPART(wk,STE_CreatedDate) AS INT )= @weekNum --@weekNum
			ORDER BY STE_CreatedDate ASC

*/

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-06-27 : 09:54:30

Well, as far as I can see the format of the procedure is ok. You should however add a "PRINT @EmailBody" right before the EXEC msdb.dbo.sp_send_dbmail and verify that the HTML looks like it should. Copy it from the management studio to an html-file and view it in a browser to see if it looks ok.

Then you need enable SQL Mail and create a mailing profile:

1) First go to Start -> Programs -> SQL 2005 -> Configuration Tools -> Surface Area Configuration, then click "config for features", then click SQL Mail and then Enable SQL Mail Procedures, then OK.

2) In Management studio, connect to your DB server, go to Management, right-click Database Mail, click Configure Database Mail and then follow the steps of the wizard.

When you have done all this you can run your procedure like this

EXEC TMP_EmailContent06262008_SP @mydate = '2008-06-27 00:00:00', @EmailRecipients = 'aakcse@aakcse.com;something@domain.com'

- Lumbago

aakcse
Aged Yak Warrior

570 Posts

Posted - 2008-06-27 : 10:39:01

Thanks again,

I am getting the below error when I try to execute

Exec [TMP_EmailContent06262008_SP] '01-02-2008','aakcse@abc.com'

Msg 245, Level 16, State 1, Procedure TMP_EmailContent06262008_SP, Line 47 Conversion failed when converting the varchar value '</TD>
<TD>' to data type int.

I have changed one line as below to avoid this error
but I need to change the other part of the code as well
can you help in changing it?

Earlier it was

SET @EmailSubject = 'Details of '+@weekNum+'th Week of Year'+ @yearNum

Now it is

	SET @EmailSubject = 'Details of '+CAST(@weekNum AS VARCHAR)+ 'th Week of Year' + CAST(@yearNum AS VARCHAR)

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-06-30 : 02:19:42

You'e on the right track here, give it another try first

- Lumbago

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-30 : 02:31:25

quote:
Originally posted by aakcse

Thanks again,

I am getting the below error when I try to execute

Exec [TMP_EmailContent06262008_SP] '01-02-2008','aakcse@abc.com'

Msg 245, Level 16, State 1, Procedure TMP_EmailContent06262008_SP, Line 47 Conversion failed when converting the varchar value '</TD>
<TD>' to data type int.

I have changed one line as below to avoid this error
but I need to change the other part of the code as well
can you help in changing it?

Earlier it was
SET @EmailSubject = 'Details of '+@weekNum+'th Week of Year'+ @yearNum

Now it is
SET @EmailSubject = 'Details of '+CAST(@weekNum AS VARCHAR(length))+ 'th Week of Year' + CAST(@yearNum AS VARCHAR(length))

Remember to specify the length while casting a variable to varchar. else it will take default length value stored in your db and return spurios results.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-30 : 02:33:00

See this for more info:-

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/04/column-length-and-data-length.aspx

aakcse
Aged Yak Warrior

570 Posts

Posted - 2008-07-02 : 06:12:24

Hi Visakh,

I have done the above changes again I am getting the same error, sorry for delay in this reply I was working on some other urgent issue.

I have done the changes for all the SP as below
SET @EmailSubject = 'Details of '+CAST(@weekNum AS VARCHAR(2))+ 'th Week of Year' + CAST(@yearNum AS VARCHAR(4))

but still getting the below error
at this line in bold <TD>' + STE_RQS_StudioID +

SELECT @EmailBody = @EmailBody +
'<TR><TD>' + STE_FacilityName + '</TD>
<TD>' + STE_RQS_StudioID + '</TD>
<TD>' + STE_CNT_ContactID_CreatedBy + '</TD>
<TD>' + STE_CreatedDate + '</TD>
<TD>' + CAST(DATEPART(wk,STE_CreatedDate) AS VARCHAR(2))+ '</TD>
<TD>' + CAST(DATENAME(DW,STE_CreatedDate) AS VARCHAR(12))+ '</TD>
'
FROM TBL_SiteMaster sm
INNER JOIN LK_RequestingStudio rs ON sm.STE_RQS_StudioID = rs.RQS_StudioID
INNER JOIN TBL_UserContacts uc ON sm.STE_CNT_ContactID_CreatedBy = uc.CNT_ContactID
WHERE CAST(DATEPART(wk,STE_CreatedDate) AS VARCHAR(2) )= @weekNum --@weekNum
ORDER BY STE_CreatedDate ASC

Msg 245, Level 16, State 1, Procedure TMP_EmailContent06262008_SP, Line 47
Conversion failed when converting the varchar value '</TD>
<TD>' to data type int.

aakcse
Aged Yak Warrior

570 Posts

Posted - 2008-07-02 : 12:41:37

I have done it as below and its working fine for me,

But I am getting only one record when I execute the html code printed by the print statement below

and I am getting more number of records when I executed the same select statement

alone

What can be done to get all the records

/*

SELECT

STE_FacilityName

,STE_RQS_StudioID

,STE_CNT_ContactID_CreatedBy

,STE_CreatedDate

,CAST(DATEPART(wk,STE_CreatedDate) AS INT ) AS WEEKNO

,DATENAME(DW,STE_CreatedDate) AS WEEKDAY

FROM TBL_SiteMaster sm

INNER JOIN LK_RequestingStudio rs ON sm.STE_RQS_StudioID = rs.RQS_StudioID

INNER JOIN TBL_UserContacts uc ON sm.STE_CNT_ContactID_CreatedBy = uc.CNT_ContactID

WHERE CAST(DATEPART(wk,STE_CreatedDate) AS INT )= 9 --@weekNum

ORDER BY STE_CreatedDate ASC

*/

SELECT @EmailBody = @EmailBody +

'<TR><TD>' + CAST(STE_FacilityName AS VARCHAR(MAX)) + '</TD>

<TD>' + CAST(STE_RQS_StudioID AS VARCHAR(MAX)) + '</TD>

<TD>' + CAST(STE_CNT_ContactID_CreatedBy AS VARCHAR(MAX)) + '</TD>

<TD>' + CAST(STE_CreatedDate AS VARCHAR(MAX)) + '</TD>

<TD>' + CAST(DATEPART(wk,STE_CreatedDate) AS VARCHAR(2))+ '</TD>

<TD>' + CAST(DATENAME(DW,STE_CreatedDate) AS VARCHAR(12))+ '</TD>

'

FROM TBL_SiteMaster sm

INNER JOIN LK_RequestingStudio rs ON sm.STE_RQS_StudioID = rs.RQS_StudioID

INNER JOIN TBL_UserContacts uc ON sm.STE_CNT_ContactID_CreatedBy = uc.CNT_ContactID

WHERE CAST(DATEPART(wk,STE_CreatedDate) AS VARCHAR(2) )= @weekNum --@weekNum

ORDER BY STE_CreatedDate ASC

SELECT @EmailBody = @EmailBody + '

</TABLE>

</BODY>

</HTML>'

PRINT @EmailBody

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources