How to avoid using a loop

[wbodger]

I have this code

declare @rowid int
declare @pid varchar(20)
declare @lr int
declare @hr int
declare @counter int
declare @counts int

set @counter = 1

SELECT @rowid=rowid, @pid=study_participant_id, @lr=lowrow, @hr=highrow, @counts=COUNT(*) FROM dbEDRN195.dbo.PSADTCombined group by rowid, study_participant_id, lowrow, highrow order by study_participant_id

while @counter<@counts
begin
if @hr=0 
(
SELECT distinct study_participant_id, phase, cast(proc_labs_psa_date as DATE) as psadate, proc_labs_psa_value, '' as valueset, '' as dateset 
FROM dbEDRN195.dbo.PSADTCombined WHERE study_participant_id = @pid and rowid = @rowid)

else

(SELECT distinct pdv.study_participant_id, phase, cast(proc_labs_psa_date as DATE) as psadate, proc_labs_psa_value , pl.valueset, pd.dateset 
FROM dbEDRN195.dbo.PSADTCombined as pdv join (select distinct study_participant_id, substring ( stuff( 
	( select ' , ' + cast(proc_labs_psa_value as varchar(max)) 
	  from dbEDRN195.dbo.PSADTCombined 
	  where study_participant_id = @pid and RowID between @lr and @hr
	  order by proc_labs_psa_date 
	  for xml path ('') ), 
	  1 , 
	  2 , 
	  '') 
	  , 2 , 1000 ) as valueset 
	  from dbEDRN195.dbo.PSADTCombined as results) as pl on pdv.study_participant_id=pl.study_participant_id 
	  join (select distinct study_participant_id, substring ( stuff( 
	  ( select ' , ' + cast(cast(proc_labs_psa_date as date) as varchar(max)) 
		from dbEDRN195.dbo.PSADTCombined 
		where study_participant_id = @pid and RowID between @lr and @hr
		order by proc_labs_psa_date 
		for xml path ('') ), 
		1 , 
		2 , 
		'') 
		, 2 , 1000 ) as dateset 
from dbEDRN195.dbo.PSADTCombined as results) as pd on pdv.study_participant_id=pd.study_participant_id 
WHERE pdv.study_participant_id = @pid and rowid= @hr
)
set @counter = @counter+1

end

which runs with no errors, but has no output in this current format. This is actually a web page, so there is scripting in and around these queries to loop through, I was just trying to test and thought I had the loops adequately mimicked. Anyway, I would like to figure out how to do this set-wise, but am coming up with a bit of a block. Anybody out there care to take a stab at this without using a loop?

Thanks,
Willie

 

[gk53]

What you are trying to achive? In you code you are looping @counts time with the same query... does not make sense

 

[Olaf Doschke]

Especially since you don't use the @counter variable at all within the queries of the while loop, you could simply remove it.

Judging the first query you'll have many records, one for each study_participant_id, but your variables will only be set to the last row result. I'd say you wanted to loop within these records, but you don't the @counts will just be the count of records of the last study_participant_id, not the count of distinct study participant ids.

If you would like to loop over the result records, make this query a subquery of a larger query and JOIN other data, JOINs are "loop" in SQL queries, because the joins are done per row of one side (table, subquery) to to another side (table, another query).

Bye, Olaf.