Cursor or Not on a table search agaist a table

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 Not on a table search agaist a table

Author

Topic

skillile
Posting Yak Master

208 Posts

Posted - 2002-10-01 : 17:26:16

I want to search PROPERTY ADDRESS AND ORDERNUM on any record in my holding table that has a flagged value.

DECLARE @x int,
@max int,
@propadd varchar(50),
@ordernum varchar(25)

SET @x = (SELECT min(eventid) FROM dbo.tblblkclosing WHERE orderfound = 0)

WHILE @x IS NOT NULL
BEGIN
SET @propadd = (SELECT propadd FROM dbo.tblblkclosing WHERE eventid=@x)
SET @ordernum = (SELECT ordernum FROM dbo.tblblkclosing WHERE eventid=@x)

IF EXISTS(SELECT * FROM dbo.tblorders WHERE ordernum LIKE '%' + @ordernum + '%' OR propertyaddress LIKE '%' + @propadd + '%')
BEGIN
UPDATE dbo.tblblkclosing
SET orderfound = 1
WHERE eventid = @x
END
SET @x = (SELECT min(eventid) FROM dbo.tblblkclosing WHERE orderfound = 0 AND eventid > @x)
END

Is there an easier way to do this with a join. And 2nd question should I consider a cursor.

Thanks

slow down to move faster...

nr
SQLTeam MVY

12543 Posts

Posted - 2002-10-02 : 03:38:21

I prefer the loop condition at the top but doesn't matter
SET @x = (SELECT min(eventid) - 1 FROM dbo.tblblkclosing WHERE orderfound = 0) -- this could be just set to 0 mayb
SET @maxx = (SELECT max(eventid) FROM dbo.tblblkclosing WHERE orderfound = 0)

WHILE @x < @max
BEGIN
SET @x = (SELECT min(eventid) FROM dbo.tblblkclosing WHERE orderfound = 0 AND eventid > @x)

I always prefer this to a cursor as you are in control of everything.
It won't be quicker - may even be slightly slower but more reliable.

To do it in a single statement - this will probably block the table though so you might prefer a combination of the two

update tblblkclosing
SET orderfound = 1
where tblblkclosing.orderfound = 0
and exists (select * from tblorders
where tblorders.ordernum like '%' + tblblkclosing.ordernum + '%'
or tblorders.propertyaddress like '%' + propadd + '%')

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

aiken
Aged Yak Warrior

525 Posts

Posted - 2002-10-02 : 12:18:17

I like nr's solution here, and I would give it a try with locking directives to see if you can convince SQL server to not lock the entire table:

update tblblkclosing WITH(ROWLOCK)
SET orderfound = 1
where tblblkclosing.orderfound = 0
and exists (select * from tblorders WITH(NOLOCK)
where tblorders.ordernum like '%' + tblblkclosing.ordernum + '%'
or tblorders.propertyaddress like '%' + propadd + '%')

(the rowlock directive is probably a good idea; PAGLOCK would be the other option. Adding NOLOCK to the select may or may not be acceptable for your application)

Cheers
-b

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources