SQL loop - 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

General SQL Server Forums

New to SQL Server Programming

SQL loop

Author

Topic

craigmacca
Posting Yak Master

142 Posts

Posted - 2008-02-14 : 06:50:53

hi i need to do a loop inside a stored procedure, just not sure how to go about it!

this is what i need

select count(TI_ID) AS REC_COUNT
FROM TRANSIT...T_TBL_TRANSIT
WHERE DATEDIFF(m, TI_Date, getdate())>12

CASE WHEN REC_COUNT > 1 THEN

===LOOP THROUGH AND DELETE 1000 AT A TIME===

DELETE top 1000 FROM TRANSIT...T_TBL_TRANSIT
WHERE DATEDIFF(m, TI_Date, getdate())>12

END CASE

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-14 : 06:58:08

[code]DECLARE @Count int,@Loop int

select @Count=count(TI_ID)
FROM TRANSIT...T_TBL_TRANSIT
WHERE DATEDIFF(m, TI_Date, getdate())>12

SET @Loop=CASE WHEN @Count/1000 >0 THEN @Count/1000
ELSE 1
END
SET @Loop=CASE WHEN @Loop <>1 AND @Count%100<>0 THEN @Loop +1 END

WHILE @Loop >0
BEGIN
DELETE top 1000 FROM TRANSIT...T_TBL_TRANSIT
WHERE DATEDIFF(m, TI_Date, getdate())>12
SET @Loop=@loop-1
END[/code]

craigmacca
Posting Yak Master

142 Posts

Posted - 2008-02-14 : 07:08:10

ok thanks, thats great, just getting a error on

DELETE top 1000 FROM TRANSIT...T_TBL_TRANSIT

Msg 102, Level 15, State 1, Line 18
Incorrect syntax near '1000'.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-14 : 07:17:29

SET ROWCOUNT 1000
DELETE FROM TRANSIT...T_TBL_TRANSIT
SET ROWCOUNT 0

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-14 : 07:27:08

or

DELETE TRANSIT...T_TBL_TRANSIT
FROM
(SELECT TOP 1000 * FROM TRANSIT...T_TBL_TRANSIT)t2
WHERE TRANSIT...T_TBL_TRANSIT.PK=t2.PK
where PK is primary key

craigmacca
Posting Yak Master

142 Posts

Posted - 2008-02-14 : 08:04:53

ok thanks,

the table i am deleting from is a access database table, and i was getting a "File sharing lock count exceeded. error

so i thought doing a loop on 1000 rows at a time would work!!

but with the code below i got this error once again, any ideas?

select @Count=count(TI_ID)
FROM TRANSIT...T_TBL_TRANSIT
WHERE DATEDIFF(m, TI_Date, getdate())>12

SET @Loop=CASE WHEN @Count/1000 >0 THEN @Count/1000
ELSE 1
END

SET @Loop=CASE WHEN @Loop <>1 AND @Count%100<>0 THEN @Loop +1 END

WHILE @Loop >0
BEGIN

SET ROWCOUNT 1000
DELETE FROM TRANSIT...T_TBL_TRANSIT
WHERE DATEDIFF(m, TI_Date, getdate())>12
SET ROWCOUNT 0

SET @Loop=@loop-1
END

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-02-14 : 11:52:09

Is this a multi user environment?
Also , have you declared the variables correctly?i.e count and loop?

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

craigmacca
Posting Yak Master

142 Posts

Posted - 2008-02-14 : 11:58:39

hi

yes this is a mutli user environment

and yes

DECLARE
@Count int,
@Loop int

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources