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

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

tsaliki
Starting Member

India
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
as

DECLARE @testjob TABLE
(
[Name] [varchar](200) ,
[NoOfPendingJobs] float,
[testjobstable] VARCHAR(MAX)
)
Insert INTO @testjob
(
name
)
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>
</TR>'

Declare @Namee nvarchar(50)
DECLARE @PENDING_JOBS_COUNT float

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
WHILE @@FETCH_STATUS = 0
BEGIN

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>'
From @PENDINGJOBS
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
End
Close cursor_Report
Deallocate cursor_Report

update @testjob
SET [testjobstable]=REPLACE(
[testjobstable]
,'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

robvolk
Most Valuable Yak

USA
15688 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  
 New 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.05 seconds. Powered By: Snitz Forums 2000