while script not working - 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 2008 Forums

Transact-SQL (2008)

while script not working

Author

Topic

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2013-01-30 : 18:32:24

Hello there.

i have the below while script.

declare @count int
set @count = (select count(transactionid) from
(select transactionid, COUNT( transactionid) as 'count_Tran'
from Ticket_Facts
group by transactionid
having COUNT(transactionid) > 1) as test)

if @count > 0

begin

exec Delete_Dup_TranIDs

When i run this code, it only runs the SP once, even tho the select
sctipt assigned to @count returns more than 1 a couple of times.

how do i make the SP run until the @count < 1

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-30 : 18:39:04

quote:
Originally posted by masterdineen

Hello there.

i have the below while script.

declare @count int
set @count = (select count(transactionid) from
(select transactionid, COUNT( transactionid) as 'count_Tran'
from Ticket_Facts
group by transactionid
having COUNT(transactionid) > 1) as test)

if @count > 0

begin

exec Delete_Dup_TranIDs

When i run this code, it only runs the SP once, even tho the select
sctipt assigned to @count returns more than 1 a couple of times.

how do i make the SP run until the @count < 1

The way the code is currently structured, I don't see any while loop at all. Can you post the full script including your while loop?

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2013-01-31 : 03:26:07

Sorry i posted what i was messing around with,

here is my while script below.

declare @count int
set @count = (select count(transactionid) from
(select transactionid, COUNT( transactionid) as 'count_Tran'
from Ticket_Facts
group by transactionid
having COUNT(transactionid) > 1) as test)

while @count > 0

begin

exec Delete_Dup_TranIDs

end

it seems to do what want it to do, but just doesnt stop.

I imagine its something small i need to change.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-01-31 : 04:00:51

quote:
Originally posted by masterdineen

Sorry i posted what i was messing around with,

here is my while script below.

declare @count int
set @count = (select count(transactionid) from
(select transactionid, COUNT( transactionid) as 'count_Tran'
from Ticket_Facts
group by transactionid
having COUNT(transactionid) > 1) as test)

while @count > 0

begin

exec Delete_Dup_TranIDs

end

it seems to do what want it to do, but just doesnt stop.

I imagine its something small i need to change.

Shouldn't it be a IF statement ?

KH
[spoiler]Time is always against us[/spoiler]

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2013-01-31 : 04:27:03

well my Stored Proc should do the job in one go, BUT. the stored procedure searches for the duplicate entrys then deletes them from the table. But when i search for duplicates again, i find more. so i have to run the SP a few times to get duplicates down to 0.

Hence why i run a while statement. But as i said the statement doesnt stop when the count is down to 0.

could you tell me where i am going wrong in the code please.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-01-31 : 05:08:26

[code]
declare @count int
set @count = (select count(transactionid) from
(select transactionid, COUNT( transactionid) as 'count_Tran'
from Ticket_Facts
group by transactionid
having COUNT(transactionid) > 1) as test)

while @count > 0

begin

exec Delete_Dup_TranIDs

set @count = (select count(transactionid) from
(select transactionid, COUNT( transactionid) as 'count_Tran'
from Ticket_Facts
group by transactionid
having COUNT(transactionid) > 1) as test)

end
[/code]

KH
[spoiler]Time is always against us[/spoiler]

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2013-01-31 : 06:44:31

ok yeah i got it,

it will keep going if the variable doesnt get a new value to work on

cheers mate.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources