Query modification - SQL Server Forums

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

SQL Server Development (2000)

Query modification

Author

Topic

sardinka
Posting Yak Master

142 Posts

Posted - 2006-12-28 : 08:57:21

I have following statement where I am using cursor. Is it possible to get read of cursor ?
DECLARE @referral_type varchar(50)
DECLARE @RefTypeID int
DECLARE referral_type CURSOR FOR
SELECT DISTINCT @site_id, @access_id,referral_type
FROM referral_upload WHERE referral_type
NOT IN (SELECT referral_type FROM referral_type where site_id=@site_id and access_id=@access_id and void<>'V')
AND Insert_date is Null
OPEN referral_type
FETCH NEXT FROM referral_type
INTO @site_id, @access_id, @referral_type

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @RefTypeID = max(referral_type_id) + 1 from referral_type where site_id = @site_id and access_id = @access_id
INSERT referral_type (site_id,
access_id,
referral_type_id,
referral_type,
void,
defaultfield)
VALUES ( @site_id, @access_id, @RefTypeID,@referral_type,'','')
FETCH NEXT FROM referral_type
INTO @site_id, @access_id, @referral_type
END
CLOSE referral_type
DEALLOCATE referral_type

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-28 : 11:18:02

Is this on SQL Server 2000? It's very easy on SQL Server 2005, so I don't want to give you a solution for 2000 if you're using 2005.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-28 : 11:31:12

Here's the solution for 2000 (it will also work on 2005, but on 2005 you can use the ROW_NUMBER function instead of the subquery that I used in this solution).

declare @site_id int, @access_id int
select @site_id = 1, @access_id = 1
declare @RefTypeID int
select @RefTypeID = max(referral_type_id) + 1 
	from referral_type 
	where site_id = @site_id and access_id = @access_id
insert referral_type (site_id,
access_id,
referral_type_id,
referral_type,
void,
defaultfield)
select @site_id, @access_id, 
	(select count(*) + @RefTypeID from referral_upload where referral_type < u.referral_type),
	referral_type, '', ''
from referral_upload u
order by u.referral_type

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources