Hi Everyone,
I'm working on a Stored Procedure which I will later use to create a report in Crystal Reports. The Report is going to be a Time Summary Report which will list timekeepers, hours, date entered, narrative,etc.
The initial problem I had was the timedesc.tddesc column size does not allow for large narratives, thus breaking them down to multiple rows.
My thought was to create a stored procedure and build a cursor to loop through each line and combine the narratives into one big column per common tindex number.
I've checked the syntax and it's correct, but I don't think I have the right information needed to give me the desired output.
This is my first attempt at creating an SP, so any help would be greatly appreciated. Thanks in advance!
Here is what I have thus far:
CREATE PROC sp_wvTimeDetail
AS BEGIN
DECLARE
@tindex integer ,
@workdate datetime,
@workhours decimal (9),
@tkinit varchar (8),
@last varchar (20),
@first varchar (20),
@tktmdate datetime ,
@narr varchar (2000),
@matter varchar (15),
@suspense varchar (2),
@fin datetime
DECLARE curnarr CURSOR FOR
SELECT
td.tindex,
tc.tworkdt,
tc.tworkhrs,
tk.tkinit,
tklast,
tkfirst,
tktmdate,
tddesc,
tc.tmatter,
wvt.ttype,
bfindt
FROM timecard tc
INNER JOIN timekeep tk on tc.ttk = tk.tkinit
INNER JOIN timedesc td on tc.tindex = td.tindex
INNER JOIN batch on tc.tbatch = batch.bbatch
INNER JOIN wv_timecard wvt on tk.tkinit = wvt.ttk
Where tk.tktmdate is null
-- open cursor
OPEN curNarr
-- Perform the first fetch.
FETCH NEXT FROM curNarr INTO @tindex, @workdate, @workhours, @tkinit, @last, @first, @tktmdate, @narr, @matter, @suspense, @fin
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- Combines multiple notes into one note
select @narr = @narr + CHAR(10)
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM curNarr INTO @tindex, @workdate, @workhours, @tkinit, @last, @first, @tktmdate, @narr, @matter, @suspense, @fin
END
CLOSE curnarr
DEALLOCATE curnarr
End
GO
Start Free Trial
