|
anilr499
Starting Member
18 Posts |
 Posted - 01/16/2013 : 09:34:03
|
I'm trying to dynamically create an HTML email using stored procedures. The actual source for the HTML is too large to fit in a varchar(8000) field..when i execute my SP it is not displaying the data in table which contains more than aprroximately 164rows...
how should i solve my problem...i have maximum of 700 rows
here is my SP:
USE [Tea]
GO
/****** Object: StoredProcedure [dbo].[SP_TS_USAGE_INPUT] Script Date: 01/10/2013 00:50:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--Created By Anil on 15 jan 2013
ALTER PROCEDURE [dbo].[SP_TS_USAGE_INPUT]
(
@BGPID varchar(50),@TOO varchar(50),@VIA varchar(500)
)
AS
DECLARE @TO VARCHAR(8000), @CC VARCHAR(8000), @BCC VARCHAR(8000), @SUBJECT VARCHAR(500), @MESSAGE VARCHAR(max), @MESSAGE2 VARCHAR(max), @cmd varchar(50)
DECLARE @CR CHAR(1), @COUNT int, @prefix varchar(50), @status varchar(20)
Declare @value_a varchar(2000), @value_b varchar(2000), @value_c varchar(2000)
Declare @admin varchar(255), @webroot varchar(500), @adminmail varchar(255), @system varchar(255)
Declare @to_rcpt varchar(2000), @cc_rcpt varchar(2000), @bcc_rcpt varchar(2000)
Declare @TotalUser VARCHAR(3), @ActiveUser VARCHAR(3), @InactiveUser VARCHAR(3), @NonUser VARCHAR(3),@Name varchar(50)
DECLARE @emp_name varchar(50), @aud_date varchar(11), @c_aud_date varchar(10), @ndiff int, @Userstatus varchar(15), @c_department varchar(100)
-- GET PARAMETERS from TSSQLREMINDER
exec sp_get_parameters 'ADMIN', 'GENERAL', @value_a output, @value_b output, @value_c output
set @admin = @value_a
set @adminmail = @value_b
exec sp_get_parameters 'MAIL', 'GENERAL', @value_a output, @value_b output, @value_c output
set @prefix = @value_a
select @to_rcpt = coalesce(par_value_a, ''), @cc_rcpt = coalesce(par_value_b, ''), @bcc_rcpt = coalesce(par_value_b, '')
from tsparameters
where par_id = 'TSSUPPORT'
SET DATEFIRST 7
SET @CR = CHAR(10)
select distinct t3.emp_name, B2.BGP_DESC AS c_department,convert(varchar(11), t1.aud_date, 106) as aud_date, t1.aud_date as c_aud_date,
datediff(d, t1.aud_date, getdate()) as ndiff, ' ' as UserStatus
into #temp from EMPLOYEE t3
LEFT JOIN EMPBIZGROUP B1 ON B1.EMP_ID = t3.EMP_ID
LEFT JOIN BIZGROUP B2 ON B2.BGP_ID = B1.BGP_ID
left join
(
select max(aud_date) as aud_date, usr_id
from tsaudit
group by usr_id
) as t1 on t1.USR_ID = t3.EMP_ALIAS
left join tsaudit t2 on t2.usr_id = t3.EMP_ALIAS
where B2.BGP_ID in
((SELECT BGP_ID FROM BIZGROUP WHERE BGP_PARENT= @BGPID) union all
(SELECT BGP_ID FROM BIZGROUP WHERE BGP_PARENT IN( SELECT BGP_ID FROM BIZGROUP WHERE BGP_PARENT= @BGPID))
union all (SELECT BGP_ID FROM BIZGROUP WHERE BGP_PARENT IN (SELECT BGP_ID FROM BIZGROUP WHERE BGP_PARENT IN( SELECT BGP_ID FROM BIZGROUP WHERE BGP_PARENT= @BGPID))))
AND (t3.emp_ter_date is null and t3.EMP_ID not in ('EMP000001','1542'))
or (t3.emp_ter_date is not null and t3.EMP_REHIRED_DATE is not null and (t3.EMP_REHIRED_DATE >= t3.emp_ter_date)and t3.EMP_ID not in ('EMP000001','1542') )
order by ndiff
update #temp
set UserStatus = 'Inactive User'
where ndiff > 7
update #temp
set UserStatus = 'Active User'
where ndiff <= 7
update #temp
set UserStatus = 'Non User'
where ndiff is null
SELECT @TotalUser = COUNT(*) FROM #temp
SELECT @ActiveUser = COUNT(*) FROM #temp where UserStatus = 'Active User'
SELECT @InactiveUser = COUNT(*) FROM #temp where UserStatus = 'Inactive User'
SELECT @NonUser = COUNT(*) FROM #temp where UserStatus = 'Non User'
SELECT @Name = BGP_DESC FROM BIZGROUP where BGP_ID = @BGPID
SET @SUBJECT = 'TeamSYNthesis Usage'
SET @MESSAGE= @CR + @CR
SET @MESSAGE= @MESSAGE + 'TeamSYNthesis usage as at ' + CONVERT(varchar(11), getdate(), 106) + ' of ' + @Name + @CR + @CR
SET @MESSAGE = @MESSAGE +
'<br/><br/>
<table border="1">
<tr align="center" style="background-color:Gray;color:white;">
<td>Total Number<br/> Of User </td>
<td><nobr>Active Users</nobr></td>
<td><nobr>Inactive Users</nobr></td>
<td><nobr>Non Users</nobr></td>
</tr>
<tr align="center">
<td>'+@TotalUser +' (100%)</td>
<td>'+@ActiveUser + ' (' + CASE WHEN @ActiveUser = '0' THEN '0' ELSE
convert(varchar,cast(round(CONVERT(INT,replace(@ActiveUser,'','0'))*100.0/CONVERT(INT, replace(@TotalUser,'','0')),1) as numeric(10,1))) END +'%)</td>
<td>'+@InactiveUser+ ' ('+ CASE WHEN @InactiveUser = '0' THEN '0' ELSE
convert(varchar,cast(round(CONVERT(INT,replace(@InactiveUser,'','0'))*100.0/CONVERT(INT, replace(@TotalUser,'','0')),1) as numeric(10,1)))END+'%)</td>
<td>'+@NonUser + ' (' + CASE WHEN @NonUser = '0' THEN '0' ELSE
convert(varchar,cast(round(CONVERT(INT,replace(@NonUser,'','0'))*100.0/CONVERT(INT, replace(@TotalUser,'','0')),1) as numeric(10,1)))END +'%)</td>
</tr>
</table>'
SET @MESSAGE = @MESSAGE +
'<br/>
<table border="1">
<tr align="center" style="background-color:Gray;color:white;">
<td> No. </td>
<td style="width:15%;"><nobr>Employee Name</nobr> </td>
<td><nobr>Department</nobr></td>
<td><nobr>Last Access Date</nobr></td>
<td><nobr>Day Difference </nobr> </td>
<td><nobr>Status</nobr> </td>
</tr>'
SET NOCOUNT ON
DECLARE MYCURSOR CURSOR FOR
select * from #temp
SET @MESSAGE2 = ''
OPEN MYCURSOR
FETCH NEXT FROM MYCURSOR
INTO @emp_name, @c_department, @aud_date, @c_aud_date, @ndiff, @Userstatus
SET @COUNT = 1
WHILE @@FETCH_STATUS=0
BEGIN
SET @MESSAGE2 = @MESSAGE2 +
'
<tr>
<td align="center"> '+convert(varchar,@COUNT)+' </td>
<td align="left"> '+@emp_name+' </td>
<td align="center"> '+isnull(@c_department,'-')+' </td>
<td align="center"> '+isnull(@aud_date,'-') +' </td>
<td align="center"> '+convert(varchar,isnull(@ndiff,'0')) +' </td>
<td align="center"> '+@Userstatus +' </td>
</tr>'
SET @COUNT = @COUNT + 1
FETCH NEXT FROM MYCURSOR
INTO @emp_name, @c_department, @aud_date, @c_aud_date, @ndiff , @Userstatus
END
CLOSE MYCURSOR
DEALLOCATE MYCURSOR
SET @MESSAGE2 = @MESSAGE2 +'</table><br/>'+ @CR + @CR +
'Kindly contact ' + @admin + ' if you need any assistance.'
SET @MESSAGE2= @MESSAGE2 + @CR+@CR+ 'Thank you,'+@CR+ @admin + @CR+
'Email : ' + @adminmail
SET @MESSAGE = @MESSAGE + @MESSAGE2
--print @MESSAGE
SET @TO = @TOO
SET @CC = @VIA
IF @TO <> ''
BEGIN
--EXEC sp_ts_sendmail @RECIPIENTS=@TO, @COPY_RECIPIENTS=@CC, @BLIND_COPY_RECIPIENTS=@BCC, @QUERY=@cmd, @SUBJECT=@SUBJECT
insert into JOBMAIL_HTML (JOM_SUBJECT, JOM_MESSAGE, JOM_TO, JOM_CC, JOM_BCC, JOM_ATTACH, JOM_DATE_CREATED, JOM_STATUS) values
(@SUBJECT, @MESSAGE, @TO, @CC , null, null, GETDATE(), 'NEW')
END
drop table #temp
Please will somebody help me
Thank you |
|
cannot display many records in html table using SP
