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 do i supress cursor select set results
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

birdie3321
Starting Member

USA
8 Posts

Posted - 02/11/2009 :  07:26:56  Show Profile  Reply with Quote
Hi all.

I have the following code:

<begin code>
DECLARE prod_cursor CURSOR
FOR
SELECT productid <--Problem child
FROM @products
OPEN prod_cursor
DECLARE @prodid_

FETCH NEXT FROM prod_cursor INTO @prodid_
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF(@@FETCH_STATUS <> -2)
BEGIN
(line by line processing)
END
FETCH NEXT FROM prod_cursor INTO @prodid_
END
CLOSE prod_cursor
DEALLOCATE prod_cursor

SELECT * from @prodid_
<end code>

Problem is that I am getting the cursor select's result first then the last select's result set. I only want the last result set. Is there a way i can supress the cursor result set?

many thanks



birdie3321

Edited by - birdie3321 on 02/11/2009 07:28:37

sakets_2000
Flowing Fount of Yak Knowledge

India
1472 Posts

Posted - 02/11/2009 :  07:40:03  Show Profile  Reply with Quote
quote:
Originally posted by birdie3321

Hi all.

I have the following code:

<begin code>
DECLARE prod_cursor CURSOR
FOR
SELECT productid <--Problem child
FROM @products
OPEN prod_cursor
DECLARE @prodid_

FETCH NEXT FROM prod_cursor INTO @prodid_
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF(@@FETCH_STATUS <> -2)
BEGIN
(line by line processing)
END
FETCH NEXT FROM prod_cursor INTO @prodid_
END
CLOSE prod_cursor
DEALLOCATE prod_cursor

SELECT * from @prodid_
<end code>

Problem is that I am getting the cursor select's result first then the last select's result set. I only want the last result set. Is there a way i can supress the cursor result set?

many thanks



birdie3321



You can use the LAST argument..

FETCH LAST FROM CURSOR_NAME

Go to Top of Page

birdie3321
Starting Member

USA
8 Posts

Posted - 02/11/2009 :  07:48:33  Show Profile  Reply with Quote
Hi, thanks for the reply. I get an error message:

fetch: The fetch type last cannot be used with forward only cursors.



birdie3321
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30098 Posts

Posted - 02/11/2009 :  07:55:51  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Mayve you can use a SET-BASED method instead?
What is "line by line processing"?



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

sakets_2000
Flowing Fount of Yak Knowledge

India
1472 Posts

Posted - 02/11/2009 :  07:59:53  Show Profile  Reply with Quote
or just define your cursor as SCROLL CURSOR
Go to Top of Page

sakets_2000
Flowing Fount of Yak Knowledge

India
1472 Posts

Posted - 02/11/2009 :  08:00:33  Show Profile  Reply with Quote
eg :

DECLARE prod_cursor SCROLL CURSOR
FOR....
Go to Top of Page

birdie3321
Starting Member

USA
8 Posts

Posted - 02/11/2009 :  08:07:03  Show Profile  Reply with Quote
sackets, i get a ton of single results sets when i do that and i have to cancel the query.

birdie3321
Go to Top of Page

sakets_2000
Flowing Fount of Yak Knowledge

India
1472 Posts

Posted - 02/11/2009 :  08:08:25  Show Profile  Reply with Quote
quote:
Originally posted by birdie3321

sackets, i get a ton of single results sets when i do that and i have to cancel the query.

birdie3321



Can you show the sql you are running?
Go to Top of Page

birdie3321
Starting Member

USA
8 Posts

Posted - 02/11/2009 :  08:13:06  Show Profile  Reply with Quote
Sure, what's your email or can i attach code to this forum? Can't email. thanks for the help

birdie3321
Go to Top of Page

sakets_2000
Flowing Fount of Yak Knowledge

India
1472 Posts

Posted - 02/11/2009 :  08:14:07  Show Profile  Reply with Quote
you can paste it here
Go to Top of Page

sridhar.dbe
Starting Member

34 Posts

Posted - 02/11/2009 :  08:18:28  Show Profile  Reply with Quote
try like this

DECLARE @prodid_ int
DECLARE prod_cursor SCROLL CURSOR
FOR
SELECT ID from a
OPEN prod_cursor
FETCH last FROM prod_cursor INTO @prodid_
select @prodid_
CLOSE prod_cursor
DEALLOCATE prod_cursor
Go to Top of Page

birdie3321
Starting Member

USA
8 Posts

Posted - 02/11/2009 :  08:21:14  Show Profile  Reply with Quote
sackets, really it is too long to paste...i'll put it up on zoho and post a link.

birdie3321
Go to Top of Page

birdie3321
Starting Member

USA
8 Posts

Posted - 02/11/2009 :  08:25:27  Show Profile  Reply with Quote
sackets, here is the url: http://viewer.zoho.com/docs/vcabYda
thanks for your help

birdie3321
Go to Top of Page

birdie3321
Starting Member

USA
8 Posts

Posted - 02/11/2009 :  08:28:32  Show Profile  Reply with Quote
Hi sridhar.dbe, yes, someone already suggested that. I get lots of single return results and have to cancel the query.

birdie3321
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30098 Posts

Posted - 02/11/2009 :  08:37:24  Show Profile  Visit SwePeso's Homepage  Reply with Quote
You can replace your cursor code with this
-- Update matching records
UPDATE		dst
SET		provid = src.provid,
		variantid = src.productid
FROM		@ProductFilter AS dst
INNER JOIN	@ProvsFilter AS src ON src.productid = dst.productid

-- Insert new records
INSERT		@ProductFilter
		(
			productid,
			provid,
			displayorder,
			VariantID,
			VariantDisplayOrder,
			ProductName,
			VariantName
		) 
SELECT		src.productid,
		src.provid,
		0,
		0,
		0,
		'',
		''
FROM		@ProvsFilter AS src
LEFT JOIN	@ProductFilter AS dst ON dst.productid = src.productid
WHERE		dst.productid IS NULL



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

sakets_2000
Flowing Fount of Yak Knowledge

India
1472 Posts

Posted - 02/11/2009 :  08:37:32  Show Profile  Reply with Quote
Your cursor is running into an infinite loop.
Change this part here in your code and give it a try,

DECLARE prov_cursor SCROLL CURSOR
FOR
	SELECT productid,provid,0,0,0,'',''
	FROM @provsfilter 

OPEN prov_cursor

DECLARE @prodid_ int, @provid_ int, @temp int, @variantid int, @temp3 int, @temp4 varchar(200),@temp5 varchar(200), @recCount int

FETCH LAST FROM prov_cursor INTO @prodid_, @provid_,@temp,@variantid,@temp3,@temp4,@temp5

WHILE (@@FETCH_STATUS <> -1)
	BEGIN
	IF(@@FETCH_STATUS <> -2)
	BEGIN
		SET @variantid = @prodid_
		SELECT * FROM @productfilter WHERE productid = @prodid_
		SET @recCount = @@rowcount
			IF(@recCount = 0) 
				BEGIN
				INSERT @productfilter (productid, provid, displayorder, VariantID, VariantDisplayOrder, ProductName, VariantName) 
				SELECT @prodid_,@provid_,@temp,@variantid,@temp3,@temp4,@temp5
				END
			ELSE
				BEGIN
				UPDATE @productfilter SET provid = @provid_, variantid = @variantid WHERE productid = @prodid_
				--print 'update made to productid: ' + cast(@prodid_ as varchar(10)) + ' with provid: ' + cast(@provid_ as varchar(10))
				END
			FETCH NEXT FROM prov_cursor INTO @prodid_, @provid_,@temp,@variantid,@temp3,@temp4,@temp5		

END 

END
--FETCH LAST FROM prov_cursor INTO @prodid_, @provid_,@temp,@variantid,@temp3,@temp4,@temp5 
CLOSE prov_cursor
DEALLOCATE prov_cursor
END

Edited by - sakets_2000 on 02/11/2009 08:39:45
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30098 Posts

Posted - 02/11/2009 :  08:42:38  Show Profile  Visit SwePeso's Homepage  Reply with Quote
@temp, @variantid, @temp3, @temp4, @temp5 are not necessary since the cursor have hardwired the values as 0 or empty space.

See my set-based suggested instead.

All the cursor-based code does, is the check if there is a line equal productid = @prodid_ .
If not (@@rowcount = 0) the you insert a record with @prodid and @provid_ values (rest are hardwired).
If there is a match, you update the record with provid = @provid_, variantid = @variantid

Think set-based!



E 12°55'05.63"
N 56°04'39.26"
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.11 seconds. Powered By: Snitz Forums 2000