Cursor or Table Variable - 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)

Cursor or Table Variable

Author

Topic

rksingh024
Yak Posting Veteran

56 Posts

Posted - 2002-06-18 : 04:28:34

Hi all,

Is it better to use cursor or the table variable.

for e.g.
I have to do some processing with every row of product table.
One way is use cursor and the other way is to have temp table with all the product_id and select one-by-one and after processing delete that row from temp table.

Ramesh

Andraax
Aged Yak Warrior

790 Posts

Posted - 2002-06-18 : 04:41:22

Hi!

If you really need to process the rows one at a time, you might as well use the cursor in my opinion. There will be very many operations if you do it with a temp table. After all, cursors are there for this type of procedure.

Nazim
A custom title

1408 Posts

Posted - 2002-06-18 : 04:46:43

Singh, what are you tring to do. if you can post problem with ddl and some dml commands am sure lot of Team members will help you to get a set based solution.

-------------------------
What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson

rksingh024
Yak Posting Veteran

56 Posts

Posted - 2002-06-18 : 05:07:55

Here is the SP. It checks the serial_no for all the products present in the table. If any of the serial_no (1-5) is missing then insert corresponding record in the table. Code is abs. right I just want to know that is it better to do this with temp table or not. In general which is preferred one.
-----------------------
CREATE PROCEDURE dbo.SPP_Check_mkt_ovw
AS
BEGIN
SET nocount ON
DECLARE @product_id int,
@cntr int

DECLARE cur_mkt CURSOR FOR
SELECT DISTINCT product_id
FROM spp_mkt_ovw
ORDER BY product_id

OPEN cur_mkt

FETCH NEXT FROM cur_mkt INTO @product_id
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @cntr = 1

WHILE @cntr < 6
BEGIN
IF NOT EXISTS ( SELECT * FROM spp_mkt_ovw
WHERE product_id=@product_id
AND serial_no= @cntr
)
BEGIN
INSERT INTO spp_mkt_ovw (product_id,serial_no)
VALUES (@product_id, @cntr)

END

SELECT @cntr = @cntr + 1
END

FETCH NEXT FROM cur_mkt INTO @product_id
END

CLOSE cur_mkt
DEALLOCATE cur_mkt

END

Edited by - rksingh024 on 06/18/2002 05:10:28

MakeYourDaddyProud

184 Posts

Posted - 2002-06-18 : 05:37:33

--No need from a temporary table
--I just decided to look for the lowest serial number
--to insert into using S1 as a driver table and S2 as an optional outer dataset

-- 1) Find the lowest missing serial_no

SELECT @cntr = min(s1.serial_no)
FROM SPP_Check_mkt_ovw s1
LEFT JOIN SPP_Check_mkt_ovw s2
ON s2.product_id = s1.product_id
AND s1.serial_no = s2.serial_no - 1
WHERE s1.product_id = @product_id
AND s2.serial_no IS NULL

--2) @product_id known in proc
-- @cntr derived from sql so

IF @@ROWCOUNT > 0
BEGIN
INSERT INTO spp_mkt_ovw (product_id,serial_no)
VALUES (@product_id, @cntr)
END

Hope that helps
Dan
www.danielsmall.com - IT Factoring

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources