SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 while script not working
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

masterdineen
Aged Yak Warrior

United Kingdom
548 Posts

Posted - 01/30/2013 :  18:32:24  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 01/30/2013 :  18:39:04  Show Profile  Reply with Quote
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?
Go to Top of Page

masterdineen
Aged Yak Warrior

United Kingdom
548 Posts

Posted - 01/31/2013 :  03:26:07  Show Profile  Reply with Quote
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.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17638 Posts

Posted - 01/31/2013 :  04:00:51  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

masterdineen
Aged Yak Warrior

United Kingdom
548 Posts

Posted - 01/31/2013 :  04:27:03  Show Profile  Reply with Quote
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.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17638 Posts

Posted - 01/31/2013 :  05:08:26  Show Profile  Reply with Quote

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



KH
Time is always against us


Edited by - khtan on 01/31/2013 05:08:49
Go to Top of Page

masterdineen
Aged Yak Warrior

United Kingdom
548 Posts

Posted - 01/31/2013 :  06:44:31  Show Profile  Reply with Quote
ok yeah i got it,

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

cheers mate.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000