Problem with While statement - 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)

Problem with While statement

Author

Topic

Sleepless
Starting Member

9 Posts

Posted - 2009-09-30 : 17:43:15

Having a problem with my While statement and wondering if what I am trying to do is outside the bounds of the While statement. What I am trying to do is return a second address if someone has two addresses. To do this I am seeking out the first address within the While statement (based on the counter) and then want it to loop through and look at the second address if there is one. This way I can, hopefully that is, send a persons info along with one householdID to my stored proc, then send the persons info along with the second householdID. This gets sent to reporting services to generate a separate report for each person. If I take the select @householdID section out in the While statement, the loop works. If I take out the While loop, the @householdID returns the first householdID. They just won't play together at the same time.

Any help would be appreciated!
Bob

declare @counter int
set @counter=0
declare @householdCount int
SELECT @householdCount=count(*) from householdmember where personID=@personID and (startdate IS NULL OR startdate <= getdate()) AND (enddate IS NULL OR enddate >= getdate()) AND mailing=1
declare @householdID int

WHILE @counter < @householdCount

select @householdID = hh.householdID
from student s
LEFT OUTER JOIN Householdmember hm WITH(NOLOCK) on hm.personID = s.personID and (hm.startdate IS NULL OR
hm.startdate <= getdate()) AND (hm.enddate IS NULL OR hm.enddate >= getdate()) AND hm.mailing=1
LEFT OUTER JOIN Household hh WITH(NOLOCK) on hh.householdID = hm.householdID
where s.personID=@personID and s.endYear=@endYear
and (select count(*) from householdmember hm2 where hm2.personID=s.personID and hm.householdID>=hm2.householdID)= @counter+1

BEGIN
exec mystoredproc @endYear,@calendarID,@grade,@teacherPersonID,@periodID,@personID,@quarter,@householdID
SET @counter = @counter+1
END

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-01 : 02:01:08

It is not easy to understand what you want to do with this [§$@%] - coding.
Please give table structure, sample data and wanted output.
I am sure there is a way to help you out.

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-01 : 02:27:03

is it not possible to sent household ids as a comma seperated list and then parse it in procedure to get individual values?

Sleepless
Starting Member

9 Posts

Posted - 2009-10-01 : 10:51:52

Thanks for the interest in my "little" problem. I'll give a little more background before giving some sample data and table structure that will hopefully help out. I had to develop a new report card this year, I used Reporting Services to do this. We have a couple of situations where a student has two primary household addresses (parents split, living half time with grandparents, etc). I need to have it print a report card for a student for each address that they are attached to. Right now it is just printing the report card for the first address it comes to for that student and then moves on to the next student.
Here are the table structures that the above SQL is pulling from. All the parameters to execute the stored proc in the while loop are coming from selections that are made on the main report, except for the householdID. I want to pass that ID in based on this loop so it knows what address info to pull. I hope I haven't confused anyone any further. I know what I want it to do, just having a hard time explaining it so I can get help! :-)

Student table
personID,studentNumber,lastname,firstname,middlename,school,calendarID,grade,endYear
931,12345678,Smith,John,Paul,Cool Middle School,650,07,2010
13543,57836529,Doe,Jane,Anne,Cool Middle School,650,07,2010

HouseholdMember table
memberID,householdID,personID,startDate,endDate,mailing
942,312,931,2005-07-01 00:00:00,NULL,1
169312,15372,931,2005-07-01 00:00:00,NULL,1
14389,5079,13543,2005-07-01 00:00:00,NULL,1

Household table
householdID,phone,phonePrivate,name,comments
312,(555)555-5555,0,Smith,NULL
15372,(555)777-7777,0,Smith/Jones,NULL
5079,(555)333-3333,0,Doe,NULL

As you can see in this example, John Smith is a household member of two households, ID's 312 and 15372. I need to be able to execute the stored proc twice so I can generate a report card for householdID 312 and then for householdID 15372.

Thanks for any and all help.
Bob

Sleepless
Starting Member

9 Posts

Posted - 2009-10-01 : 14:08:01

Update:
Well after playing around with this crazy thing for another couple of hours I think I got it to work. For grins I moved the BEGIN statement to directly after the While statement instead of just before I execute my stored proc. This allows the whole statement to work, but only in SQL Query Analyzer. If I run it in reporting services then I do not get anything returned. GRRRR. Running out of creative ideas on how to handle this situation. By the way we are running SQL 2000 SP3.

Bob

Sleepless
Starting Member

9 Posts

Posted - 2009-10-01 : 15:16:01

Dumb thumbs....had some hard coded parameters that I forgot to take out when I copied it over to the report. So now I get data returned on the reporting services side. Unfortunately I am not getting two report cards for John Smith, just one with the first address. I may have found the 953rd way to circle back to the same problem... :-)
I'm running out of ideas on how to handle this situation.

Bob

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-01 : 15:33:36

cant you bring both address details onto report and then group on householdid and show them on different pages to give separate page look

Sleepless
Starting Member

9 Posts

Posted - 2009-10-01 : 16:02:18

I had not thought of that. Right now I am grouping on the personID. The way my "old" query ran, I returned both addresses to the report but it only used one. I'll run the old proc and change the grouping on the report to the householdID and see what happens.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-01 : 16:04:51

good. make sure you check option insert page break after group in grouping tab properties

Sleepless
Starting Member

9 Posts

Posted - 2009-10-01 : 16:13:27

OMFG!!!! That worked!!!! I won't tell you how many DAYS I have been trying to come up with a coding solution to this problem. Funny how the easiest solutions are sometimes right under your nose sometimes. Thank you, thank you, thank you!

Bob

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-01 : 16:14:36

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources