can this be done without a cursor? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2000)

can this be done without a cursor?

Author

Topic

00kevin
Yak Posting Veteran

78 Posts

Posted - 2006-01-03 : 11:14:58

is there a way to speed this up and get rid of the cursor?

declare @callee_id int
DECLARE cur CURSOR FOR
select callee_id from PRINT_LOG where request_id = @id
OPEN cur
FETCH NEXT FROM cur INTO @callee_id

WHILE @@FETCH_STATUS = 0
BEGIN

insert into #TEMP_CALL_SHEETS
(
callee_id
, branch_id
, bucket_id
, ,bucket_id

)
SELECT TOP 10
callee_id
,branch_id
,bucket_id
,branch

FROM vwCallSheet
where callee_id = @callee_id
order by completed_date desc
FETCH NEXT FROM cur INTO @callee_id

END

CLOSE cur
DEALLOCATE cur

select * from #TEMP_CALL_SHEETS

drop table #temp_call_sheets

Kristen
Test

22859 Posts

Posted - 2006-01-03 : 11:23:20

Dunno if this would work (not sure if PL.callee_id is in scope in the function call)


CREATE FUNCTION MyFunction(@callee_id int)
RETURNS TABLE
AS
    RETURN
         SELECT TOP 10
             callee_id
             ,branch_id
             ,bucket_id
             ,branch
         FROM vwCallSheet
         WHERE callee_id = @callee_id
         ORDER BY completed_date DESC
GO

INSERT INTO #TEMP_CALL_SHEETS
(
callee_id 
, branch_id 
, bucket_id 
,bucket_id
)
SELECT CS.*
FROM PRINT_LOG AS PL
     JOIN dbo.MyFunction(PL.callee_id) AS CS
WHERE PL.request_id = @id

Kristen

00kevin
Yak Posting Veteran

78 Posts

Posted - 2006-01-03 : 11:40:25

well that is a good idea but it doesn't like (PL.callee_id)

Incorrect syntax near '.'.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-04 : 00:45:25

Will point 2 here help you?
http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-01-04 : 07:10:04

"it doesn't like (PL.callee_id)"

I was half expecting that. Bother!

Kristen

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-01-05 : 09:47:44

What combination of columns would be unique row identifier for vwCallSheet?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-01-05 : 10:14:02

This solution uses a temp table with an IDENTITY column to create a row sequence. After you have the row sequence, the rest is easy.


create table #TEMP_CALL_SHEETS
(
-- Sequence is to preserve order
-- by completed_date within callee_id
Sequence	int		not null
	identity(1,1) primary key clustered,
callee_id	int		not null,
branch_id	int		not null,
bucket_id	int		not null,
branch		int		not null,
completed_date	datetime	not null
)


insert into #TEMP_CALL_SHEETS
	(
	callee_id,
	branch_id, 
	bucket_id, 
	branch,
	completed_date
	)
SELECT
	-- top used to ensure correct sequence
	top 100 percent
	callee_id,
	branch_id, 
	bucket_id, 
	branch,
	completed_date
FROM
	vwCallSheet
order by
	callee_id,
	completed_date desc

select
	a.callee_id,
	a.branch_id, 
	a.bucket_id, 
	a.branch
from
	#TEMP_CALL_SHEETS a
	join
	(
	select
		bb.callee_id
		-- last possible sequence to get 10 or less
		Last_Sequence = min(bb.Sequence)+9
	from
		#TEMP_CALL_SHEETS bb
	group by
		bb.CALLID
	) b
	on	a.callee_id = b.callee_id	and
		a.Sequence <= b.Last_Sequence
order by
	a.callee_id,
	a.Sequence

CODO ERGO SUM

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2006-01-05 : 19:42:50

How about something like this:

--create tally table to insure proper join on callee_id
create table dbo.numbers ( n int primary key)
insert dbo.numbers
select 1 union all select 2 union all
select 3 union all select 4 union all
select 5 union all select 6 union all
select 7 union all select 8 union all
select 9 union all select 10 
-- as many as you might need

go

select v.*
from
(
	select top 100 percent n.n, v.callee_id, v.branch_id, v.bucket_id, v.branch, v.completed_date
	from vwCallSheet v, numbers n
	group by n.n, v.callee_id, v.branch_id, v.bucket_id, v.branch
	having n.n <= count(o.orderid) and n.n <= 10
	order by completed_date desc
) v
join PRINT_LOG o on o.callee_id = v.callee_id and o.request_id = @id
order by v.callee_id, v.completed_date desc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources