Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
30421 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
30421 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
30421 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  
 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.1 seconds. Powered By: Snitz Forums 2000