How to modify this stored procedure

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

Transact-SQL (2005)

How to modify this stored procedure

Author

Topic

tsaliki
Starting Member

19 Posts

Posted - 2012-10-10 : 08:54:46

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

15732 Posts

Posted - 2012-10-10 : 14:21:00

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?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources