looping based on java..

Question

looping based on java..

achen9291

SSC-Addicted

Points: 460
More actions
June 28, 2012 at 10:26 am

#276984

Okay this is based off my knowledge of java because i don't know how looping works for sql. so if you know java you would understand the code alittle more. it's not in the correct syntax, could someone help me fix the syntax so that it's closer to working? i don't know about begins or ends or continues or whatever. take note of the selectnew inbetween the smiley faces where it's being replaced by a new number from every loop. Am I on the right track? or is this completely wrong? :/
DECLARE @loopcount int
DECLARE @total int
DECLARE @selectnew int
SET @loopcount = 0
SET @total = 9
SET @selectnew = 1
WHILE(@loopcount < @total)
IF(@selectnew == 1)
Select ClientUserName, DestHost, count(DestHost) counts from #ProxyLog_record
where ClientUserName =(Select top 1 ClientUserName from #ProxyLog_count_2)
Group by ClientUserName, DestHost order by counts desc
Select ClientUserName, DestHost, count(DestHost) counts from #ProxyLog_record
where ClientUserName =(Select top 1 ClientUserName from #ProxyLog_count_2 where ClientUserName not in
(Select top 1 ClientUserName from #ProxyLog_count_2))
Group by ClientUserName, DestHost order by counts desc
SET @loopcount = @loopcount + 1
SET @selectnew = @selectnew + 1
ELSE
Select ClientUserName, DestHost, count(DestHost) counts from #ProxyLog_record
where ClientUserName =(Select top 1 ClientUserName from #ProxyLog_count_2 where ClientUserName not in
(Select top (:-)selectnew:-)) ClientUserName from #ProxyLog_count_2))
Group by ClientUserName, DestHost order by counts desc
SET @loopcount = @loopcount + 1
SET @selectnew = @selectnew + 1

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

It is hard to say if you are on the right track or not. I would lean towards not because looping in t-sql is horribly slow. This is now the 3rd time I have mentioned that you need to use an order by when selecting top.

Perhaps what you need is to step back and explain clearly what you are trying to do. Then take a look at the first link in my signature about best practices when posting questions. It seems you have an agenda of some sort but you are asking for help piece meal. If you could explain the entire process you are trying to get working we can provide a better solution for you.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

rhythmk SSCertifiable Points: 7271 More actions · Answer 2

achen9291 (6/28/2012)
Okay this is based off my knowledge of java because i don't know how looping works for sql. so if you know java you would understand the code alittle more. it's not in the correct syntax, could someone help me fix the syntax so that it's closer to working? i don't know about begins or ends or continues or whatever. take note of the selectnew inbetween the smiley faces where it's being replaced by a new number from every loop. Am I on the right track? or is this completely wrong? :/
DECLARE @loopcount int
DECLARE @total int
DECLARE @selectnew int
SET @loopcount = 0
SET @total = 9
SET @selectnew = 1
WHILE(@loopcount < @total)
IF(@selectnew == 1)
Select ClientUserName, DestHost, count(DestHost) counts from #ProxyLog_record
where ClientUserName =(Select top 1 ClientUserName from #ProxyLog_count_2)
Group by ClientUserName, DestHost order by counts desc
Select ClientUserName, DestHost, count(DestHost) counts from #ProxyLog_record
where ClientUserName =(Select top 1 ClientUserName from #ProxyLog_count_2 where ClientUserName not in
(Select top 1 ClientUserName from #ProxyLog_count_2))
Group by ClientUserName, DestHost order by counts desc
SET @loopcount = @loopcount + 1
SET @selectnew = @selectnew + 1
ELSE
Select ClientUserName, DestHost, count(DestHost) counts from #ProxyLog_record
where ClientUserName =(Select top 1 ClientUserName from #ProxyLog_count_2 where ClientUserName not in
(Select top (:-)selectnew:-)) ClientUserName from #ProxyLog_count_2))
Group by ClientUserName, DestHost order by counts desc
SET @loopcount = @loopcount + 1
SET @selectnew = @selectnew + 1

Oh GOD....means you didn't receive the solution till now but as I told you earlier that please put your requirement and also furnish the DDL and sample data.

--rhythmk
------------------------------------------------------------------
To post your question use below link

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂