cannot display many records in html table using SP

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 2008 Forums

SQL Server Administration (2008)

cannot display many records in html table using SP

Author

Topic

anilr499
Starting Member

18 Posts

Posted - 2013-01-16 : 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 +
' 
<table border="1">
<tr align="center" style="background-color:Gray;color:white;">
<td>Total Number Of User </td>
<td>Active Users</td>
<td>Inactive Users</td>
<td>Non Users</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 +
' 
<table border="1">
<tr align="center" style="background-color:Gray;color:white;">
<td> No. </td>
<td style="width:15%;">Employee Name </td>
<td>Department</td>
<td>Last Access Date</td>
<td>Day Difference </td>
<td>Status </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> '+ @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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources