Loop? - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Loop?

Author

Topic

TGarmon
Starting Member

18 Posts

Posted - 2002-03-07 : 14:04:22

Hello,

I currently have the @confirmation as a static setting. I would like to select mulitple confirmation numbers from a table and loop each returned confirmation number over the associate scoring section to the end of the update section. How can I do this? Thanks for your help. I looked at BOL, but I am still not sure.

Thanks,
John

DECLARE @totalPart INT,
@confirmation varchar(18),
@DeptTotal INT,
@RestTotal INT,
@LOTotal INT,
@E_Score decimal(15,4),
@A_Score decimal(15,4),
@D_Score decimal(15,4)
SET @totalPart = NULL
SET @DeptTotal = NULL
SET @RestTotal = NULL
SET @LOTotal = NULL
SET @E_Score = NULL
SET @A_Score = NULL
SET @D_Score = NULL
SET @confirmation = '1/1/2002-203-1'

-- Associate Score
SET @totalPart=(SELECT count(c2_3)+ count(c2_15)+ count(c2_16)+ count(c2_17)+count(c3_1)+count(c3_2)+count(c3_3)
+count(c3_4)+count(c3_5)+count(c3_6)+count(c3_7)+count(c3_8)+count(c3_9) +count(c5_1) +count(c5_2)
+count(c5_3)+count(c5_4) +count(c5_5)+count(c9_2) + count(c10_1) +count(c11_1)+count(c13_1)
+count(c13_2)+count(c13_3)+count(c13_4)
FROM dataquestionnaire WHERE confirmation =@confirmation)

SET @A_Score=(SELECT((CONVERT(numeric,isnull(c2_3,0))+ CONVERT(numeric,isnull(c2_15,0))+ CONVERT(numeric,isnull(c2_16,0))+ CONVERT(numeric,isnull(c2_17,0))+
CONVERT(numeric,isnull(c3_1,0))+ CONVERT(numeric,isnull(c3_2,0))+ CONVERT(numeric,isnull(c3_3,0))+ CONVERT(numeric,isnull(c3_4,0))+
CONVERT(numeric,isnull(c3_5,0))+ CONVERT(numeric,isnull(c3_6,0))+ CONVERT(numeric,isnull(c3_7,0))+ CONVERT(numeric,isnull(c3_8,0))+
CONVERT(numeric,isnull(c3_9,0))+ CONVERT(numeric,isnull(c5_1,0))+ CONVERT(numeric,isnull(c5_2,0))+ CONVERT(numeric,isnull(c5_3,0))+
CONVERT(numeric,isnull(c5_4,0))+ CONVERT(numeric,isnull(c5_5,0))+ CONVERT(numeric,isnull(c9_2,0))+ CONVERT(numeric,isnull(c10_1,0))+
CONVERT(numeric,isnull(c11_1,0))+ CONVERT(numeric,isnull(c13_1,0))+ CONVERT(numeric,isnull(c13_2,0))+ CONVERT(numeric,isnull(c13_3,0))+
CONVERT(numeric,isnull(c13_4,0))) / @totalpart)
AS Score_A
FROM dataquestionnaire WHERE confirmation = @confirmation)
-- End Associate Score
/*
--Used to check math for statements from above.
SELECT confirmation,c2_3,c2_15,c2_16,c2_17,c3_1,c3_2,c3_3,c3_4,c3_5,c3_6,c3_7,c3_8,c3_9,
c5_1,c5_2,c5_3,c5_4,c5_5,c9_2,c10_1,c11_1,c13_1,c13_2,c13_3,c13_4
FROM dataquestionnaire WHERE confirmation = @confirmation
*/
--Departmental Score
SET @DeptTotal=(SELECT count(c7_1)+ count(c7_2)+ count(c7_3)+ count(c7_4)+ count(c9_1)+ count(c13_5)
FROM dataquestionnaire WHERE confirmation =@confirmation)

SET @D_Score=(SELECT((CONVERT(numeric,isnull(c7_1,0))+ CONVERT(numeric,isnull(c7_2,0))+ CONVERT(numeric,isnull(c7_3,0))+
CONVERT(numeric,isnull(c7_4,0))+ CONVERT(numeric,isnull(c9_1,0))+ CONVERT(numeric,isnull(c13_5,0)))
/ @DeptTotal) AS Score_D
FROM dataquestionnaire WHERE confirmation = @confirmation)

Set @E_Score = (Select (@A_Score+@D_Score)/2)

PRINT @E_Score
-- End Department Score
--Encounter Score is a combination of both associate score and department score
--End Encounter Score

begin transaction
Select Score_E from dataquestionnaire where confirmation = @confirmation
UPDATE DataQuestionnaire
Set Score_E = @E_Score, Score_A = @A_Score
Where confirmation = confirmation and score_a is null
Select Score_E from dataquestionnaire where confirmation = @confirmation
rollback
Select Score_E from dataquestionnaire where confirmation = @confirmation

Jay99

468 Posts

Posted - 2002-03-07 : 14:33:26


create table #confirmations
(
    counter int identity(1,1),
    confirmation varchar(18)
)

declare @counter int, @maxcounter int

insert #confirmations
select confirmation
from myconfirmationtable
where mycritera = 'are met'

select @counter=1, @maxcounter = max(counter)
from #confirmations

while @counter <= @maxcounter
begin

select blah, blah, blah
from dataquestionaire d inner join #confirmations c
        on d.confirmation = c.confirmation
where c.counter = @counter

--blah...
--blah...

set @counter = @counter + 1

end

HOWEVER . . . this is iterative processing, and as you know, godly database folk think set-based and leave the iterative junk to the mere mortals. I have provide this 'loop' cause that's what you asked for; however, you should challenge yourself to come up with a set-based solution to you problem.

Jay

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-03-07 : 14:41:27

Jay99 is right, you should attempt a set-based solution to this, it certainly looks feasible.

You should also explore using computed columns; I notice you add up a good deal of columns more than once in your dml. You'd save a lot of typing and make your code cleaner.

TGarmon
Starting Member

18 Posts

Posted - 2002-03-07 : 16:55:58

Well, I will admitt that I am not familiar with SQL advanced topics. Where could I get information about a set-based solution?

I am running MSSQL 7.0. I believe that I do not have access to computed columns since that feature is only in SQL 2000.

Thanks,
John

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-03-07 : 17:13:11

John,

Don't even bother with computed columns!

Make views instead...

DavidM

Tomorrow is the same day as Today was the day before.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-07 : 17:33:36

quote:
John,

Don't even bother with computed columns!

Make views instead...

What's wrong with computed columns David?

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-03-07 : 18:18:17

They are redundant...

Views provide the same functionality with the benefit of data independance.

If the computation changes, I would rather just alter the view than the base table itself.

Although the ability to index a computed column in SQL2K on the base table could possible sway me in some instances...

DavidM

Tomorrow is the same day as Today was the day before.

TGarmon
Starting Member

18 Posts

Posted - 2002-03-08 : 07:54:16

Thank you for the comments.

I still have one remaining question. What is aset-based solution?

Thanks,
John

Jay99

468 Posts

Posted - 2002-03-08 : 08:49:02

In a nutshell . . .

Iterative:


for each currentrow in table
    update table
    set field = value
    where row = currentrow
next currentrow

Set:


update table
set field = value

SQL Server (and all RDBMSs) function best with set-based operations. In the two example above(psuedo-code of course), you end up with the same results. However, the execution plan of the iterative solution will reveal N update operations (where N = number of rows) and the set based solution will only take one update. The difference in execution time will be roughly linear, depending one other factors such as indexes, hardware config etc.

The example I gave you, built a #confirmations temp table to store your various comfirmation dates. Rather than loop through and execute for eac confirmation, try to find a way to inner join the #confirmations table to achieve your results in one execution.

Jay

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources