Cursor return value - 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 2005 Forums

Transact-SQL (2005)

Cursor return value

Author

Topic

nguyenl
Posting Yak Master

128 Posts

Posted - 2008-08-05 : 19:41:13

Trying to get my cursor to return values but it is not working. Please help.

ALTER PROCEDURE [dbo].[getsales_x](@propid INTEGER)

AS

DECLARE @aSalePrice INT
DECLARE @aSaleDate VARCHAR(10)
DECLARE @aSeller VARCHAR(50)
DECLARE @aBuyer VARCHAR(50)
DECLARE @aExcise VARCHAR(10)
DECLARE @aRcrdg VARCHAR(15)
DECLARE @aMulti VARCHAR(1)

Returns
(@aSaleDate VARCHAR(10),
@aSalePrice INT,
@aExcise VARCHAR(10),
@aRcrdg VARCHAR(15),
@aSeller VARCHAR(50),
@aBuyer VARCHAR(50),
@aMulti VARCHAR(1))

RETURN
@aSaleDate,
@aSalePrice,
@aExcise,
@aRcrdg,
@aSeller,
@aBuyer,
@aMulti

DECLARE PARCEL_CURSOR CURSOR FOR

SELECT

sale_date,
sale_price,
excise_id,
rcrdg_number,
seller_name,
buyer_name,
mult_parcl

FROM
PARCEL LEFT JOIN
PAR_SALES ON PARCEL.LINK_ID = PAR_SALES.LINK_ID

WHERE parcel.property_id = @propid
AND parcel.parcel_year = 0
order by 1 DESC

OPEN residence_CURSOR

FETCH next FROM PARCEL_CURSOR INTO

@aSaleDate,
@aSalePrice,
@aExcise,
@aRcrdg,
@aSeller,
@aBuyer,
@aMulti

WHILE @@fetch_status = 0

BEGIN

FETCH next FROM PARCEL_CURSOR INTO

@aSaleDate,
@aSalePrice,
@aExcise,
@aRcrdg,
@aSeller,
@aBuyer,
@aMulti

END

CLOSE PARCEL_CURSOR

DEALLOCATE PARCEL_CURSOR

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-05 : 19:47:18

I don't see why you are using a cursor, but for stored procedures, you need to OUTPUT the variables not RETURN them.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

nguyenl
Posting Yak Master

128 Posts

Posted - 2008-08-06 : 11:51:23

This Cursor is used to populate a grid that is embedded in an application (its graphical interface). The grid has these fields:

@aSaleDate,
@aSalePrice,
@aExcise,
@aRcrdg,
@aSeller,
@aBuyer,
@aMulti.

Thus, I am trying to select data from the table and then output them into the correct fields on the grid.

Does this make a difference?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-06 : 12:06:57

You still don't need a cursor according to what you've posted.

Why aren't you just doing this:


ALTER PROCEDURE [dbo].[getsales_x]
(@propid int, @aSalePrice int OUTPUT, @aSaleDate varchar(10) OUTPUT, ...)
AS

SELECT
	@aSaleDate = sale_date,
	@aSalePrice = sale_price,
	@aExcise = excise_id,
	@aRcrdg = rcrdg_number,
	@aSeller = seller_name,
	@aBuyer = buyer_name,
	@aMulti = mult_parcl
FROM PARCEL 
LEFT JOIN PAR_SALES 
	ON PARCEL.LINK_ID = PAR_SALES.LINK_ID
WHERE 
	parcel.property_id = @propid AND 
	parcel.parcel_year = 0
ORDER BY sale_date DESC

I got bored writing the output parameters, so you'll need to finish that.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-06 : 12:09:44

Well you are not doing anything in your cursor body. Only populating the local variables...

@aSaleDate,
@aSalePrice,
@aExcise,
@aRcrdg,
@aSeller,
@aBuyer,
@aMulti

As Tara says, you can't .............. Oh wait, she's posted the solution already....

-------------
Charlie

nguyenl
Posting Yak Master

128 Posts

Posted - 2008-08-06 : 13:20:39

If the result set from the query does return more than one row, wouldn't I need to address each row in a loop, which would be done with a cursor?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-06 : 13:28:22

quote:
Originally posted by nguyenl

If the result set from the query does return more than one row, wouldn't I need to address each row in a loop, which would be done with a cursor?

Nope the SELECT statement will return all the records from your table(s) which satisfies the condition specified. But definitely you cant hold all the record values in each variable. If your intention is to get all the record values which satisfies the condition, you need to just put it in a temporary table or table variable.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-06 : 13:30:15

quote:
Originally posted by nguyenl

If the result set from the query does return more than one row, wouldn't I need to address each row in a loop, which would be done with a cursor?

Variables can only hold one value at a time. You can't send a variable back to the application multiple times, so a cursor isn't going to work.

Can your application receive a result set (such as from a DataTable) instead?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

nguyenl
Posting Yak Master

128 Posts

Posted - 2008-08-06 : 18:15:19

Yes it can

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-06 : 18:18:34

Then just use this and handle the rest in your application:


ALTER PROCEDURE [dbo].[getsales_x]
(@propid int)
AS

SELECT
	sale_date,
	sale_price,
	excise_id,
	rcrdg_number,
	seller_name,
	buyer_name,
	mult_parcl
FROM PARCEL 
LEFT JOIN PAR_SALES 
	ON PARCEL.LINK_ID = PAR_SALES.LINK_ID
WHERE 
	parcel.property_id = @propid AND 
	parcel.parcel_year = 0
ORDER BY sale_date DESC

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources