Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 How to modify this stored procedure
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

19 Posts

Posted - 10/10/2012 :  08:54:46  Show Profile  Reply with Quote
I have a table:

create table testjob


jobid int,
jobname varchar(100),
time float,
name varchar(50)

insert into testjob values ( 1001,'java work',4.5,'arjun')
insert into testjob values ( 1005,'sql work',10,'arjun')
insert into testjob values ( 1010,'.net work',7.5,'arjun')
insert into testjob values ( 1040,'java work',5.5,'ravi')
insert into testjob values ( 1023,'php work',2.5,'arjun')
insert into testjob values ( 1027,'.net work',3.5,'ravi')

and i wrote a proc as below:

create proc pr_testjobhtml

[Name] [varchar](200) ,
[NoOfPendingJobs] float,
[testjobstable] VARCHAR(MAX)
Insert INTO @testjob
select name From testjob

declare @myHeader varchar(max)
declare @myJobs varchar(max)
declare @myJobs1 varchar(max)
declare @testjobstable varchar(max)
SET @myHeader='<html><head><title>Test html report</title><style>.'+'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;}'+ '</style></head><body>'
+'<TABLE id=''Table1'' cellSpacing=''0'' cellPadding=''2'' width=''100%'' border=''0'' borderColorDark=''white'' borderColorLight=''#a0b4d7''>'

SET @myJobs='<TR><TD vAlign=''top'' align=''left'' class=''Heading'' bgcolor=''#6890c8'' colspan=''4''>Job Description</TD></TR>
<TR><TD vAlign=''middle'' align=''left'' class=''Heading'' bgcolor=''#6890c8''>JobID</TD>
<TD vAlign=''middle'' align=''left'' class=''Heading'' bgcolor=''#6890c8''>Jobname</TD>
<TD vAlign=''middle'' align=''left'' class=''Heading'' bgcolor=''#6890c8''>Hours Spent</TD>
<TD vAlign=''middle'' align=''left'' class=''Heading'' bgcolor=''#6890c8''>Date</TD>

Declare @Namee nvarchar(50)

declare @PENDINGJOBS table(
JobID [int], Jobname varchar (300) ,[Date] [datetime] ,[Time] float,Comments varchar (1000),name varchar(300)


insert into @PENDINGJOBS ([JobID],[Jobname],[Date],[Time] ,[Comments],name)

select JobID,Jobname,[Date],[Time],Comments,name FROM [testjob]

Declare cursor_Report cursor for select name,Sum([time]) as noofpendingjobs from @PENDINGJOBS group by Name
Open cursor_Report
Fetch next from cursor_Report into @Namee,@PENDING_JOBS_COUNT

SET @myJobs1=@myJobs
Select @myJobs1=@myJobs1+ '<TR vAlign=''middle'' align=''left'' class=''Content''><TD>'
+ CONVERT(CHAR(12),[JobID],3) +'</TD><TD>'+
ISNULL([Jobname],' ') +'</TD><TD NOWRAP>'+
CONVERT(VARCHAR(15),ISNULL([Time],0)) +'</TD><TD>'+
CONVERT(CHAR(12),[Date],3) +' </TD><TD>' +'</TD></TR>'
WHERE Name=@Namee

UPDATE @testjob
SET [testjobstable]=@myJobs1,[Noofpendingjobs]=@PENDING_JOBS_COUNT
WHERE Name=@Namee

Fetch next from cursor_Report into @namee,@PENDING_JOBS_COUNT
Close cursor_Report
Deallocate cursor_Report

update @testjob
SET [testjobstable]=REPLACE(
,'Job Description'
, 'Recruiter Activities : '+ISNULL(Name,'')
+' ('+CONVERT(VARCHAR(25),ISNULL([NoOfPendingJobs],0))+' Hours Spent)'

SET @myJobs1=@myHeader+ISNULL(@myJobs,'')+'</TABLE></body></html>'
select @testjobstable=''
select @testjobstable=@testjobstable+'<TABLE cellSpacing=''0'' cellPadding=''0'' width=''100%'' border=''1'' borderColorDark=''white'' borderColorLight=''#a0b4d7''><tr><td colspan=''4''>'+isnull([testjobstable],'') +'</td><tr><td colspan=''4''></td></tr></TABLE><br>' FROM @testjob

select * from @testjob

select @testjobstable=@myHeader+@testjobstable+'</TABLE></body></html>'

SELECT @testjobstable as testjobshtml

when i execute the proc i am getting the output as below:

Name Noofpendingjobs testjobstable
Arjun 24.5 <tr><td>...
Arjun 24.5
arjun 24.5
ravi 9
arjun 24.5
ravi 9

Instead i want the output as : ( i,e i want one time only instead of repeating the names )

name noofpendingjobs testjobstable
arjun 24.5 <tr><td>...
ravi 9

Most Valuable Yak

15732 Posts

Posted - 10/10/2012 :  14:21:00  Show Profile  Visit robvolk's Homepage  Reply with Quote
1. I couldn't run your stored procedure because you reference columns "Date" and "Comments" that don't exist in the testjob table.
2. Assuming you even need them, they're not referenced in the cursor
3. You don't need a cursor for this, and probably don't need the table variables either. Is this the only data you want to return?

Since this is an HTML table I'm assuming you're presenting this as a web report, or to be sent via email. Please confirm?
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000