Temp table instead of cursors - 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)

Temp table instead of cursors

Author

Topic

nguyenl
Posting Yak Master

128 Posts

Posted - 2008-08-04 : 11:46:46

I am trying to find an alternative for using cursor. I heard that I can use temp tables. Can some please show me how to convert this procedure to use temp tables instead of a cursor?

Thanks

CREATE 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)

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

RETURN

END

CLOSE PARCEL_CURSOR

DEALLOCATE PARCEL_CURSOR

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-08-04 : 12:08:54

What is your procedure doing at all? Or what do you expect it to do?

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-08-04 : 12:26:24

quote:
Originally posted by nguyenl

I heard that I can use temp tables.

who told you? your teacher or your boss?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-04 : 13:02:09

i cant understand whats the purpose of this procedure. Were you learning cursor?

nguyenl
Posting Yak Master

128 Posts

Posted - 2008-08-04 : 13:49:42

This is the original procedure which i converted into SQL using a cursor. Yes, Cursors are new to me. I am trying to learn them. Any help would be great.

Thanks,

CREATE PROCEDURE getsales_x(propid INTEGER
)

RETURNING
CHAR(10),
INTEGER,
CHAR(10),
CHAR(15),
CHAR(50),
CHAR(50),
CHAR(01);

DEFINE aSalePrice INTEGER;
DEFINE aSaleDate CHAR(10);
DEFINE aSeller CHAR(50);
DEFINE aBuyer CHAR(50);
DEFINE aExcise CHAR(10);
DEFINE aRcrdg CHAR(15);
DEFINE aMulti CHAR(01);

FOREACH
SELECT
sale_date,
sale_price,
excise_id,
rcrdg_number,
seller_name,
buyer_name,
mult_parcl
INTO
aSaleDate,
aSalePrice,
aExcise,
aRcrdg,
aSeller,
aBuyer,
aMulti
FROM
Parcel
WHERE parcel.property_id = propid
AND parcel.parcel_year = 0
order by 1 DESC

RETURN
aSaleDate,
aSalePrice,
aExcise,
aRcrdg,
aSeller,
aBuyer,
aMulti
with RESUME;

END FOREACH;

END PROCEDURE;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-04 : 13:54:58

Then why do you need a temp table? you're nothing more than simply selecting records from your table which satisfying the condition.nothing more than

SELECT fields
FROM Table
WHERE condition

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources