| Author |
 Topic  |
|
|
masterdineen
Aged Yak Warrior
United Kingdom
517 Posts |
 Posted - 01/30/2013 : 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
Flowing Fount of Yak Knowledge
1512 Posts |
Posted - 01/30/2013 : 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
United Kingdom
517 Posts |
Posted - 01/31/2013 : 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)
Singapore
16746 Posts |
Posted - 01/31/2013 : 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
Time is always against us
|
|
|
|
masterdineen
Aged Yak Warrior
United Kingdom
517 Posts |
Posted - 01/31/2013 : 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)
Singapore
16746 Posts |
Posted - 01/31/2013 : 05:08:26
|
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 |
|
|
|
masterdineen
Aged Yak Warrior
United Kingdom
517 Posts |
Posted - 01/31/2013 : 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. |
|
|
| |
Topic |
|
while script not working
