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.
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 intset @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 > 0beginexec Delete_Dup_TranIDsWhen i run this code, it only runs the SP once, even tho the selectsctipt 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 intset @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 > 0beginexec Delete_Dup_TranIDsWhen i run this code, it only runs the SP once, even tho the selectsctipt 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 intset @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 > 0beginexec Delete_Dup_TranIDsend 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 intset @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 > 0beginexec Delete_Dup_TranIDsend 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 intset @count = (select count(transactionid) from(select transactionid, COUNT( transactionid) as 'count_Tran'from Ticket_Factsgroup by transactionidhaving COUNT(transactionid) > 1) as test)while @count > 0beginexec Delete_Dup_TranIDsset @count = (select count(transactionid) from(select transactionid, COUNT( transactionid) as 'count_Tran'from Ticket_Factsgroup by transactionidhaving 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 oncheers mate. |
|
|
|
|
|
|
|