while ((select * from x ) is not null)

Question

Post reply

while ((select * from x ) is not null)

newbie_dev

SSCommitted

Points: 1916
More actions
January 13, 2009 at 9:50 am

#196122

Hi
I have to do a loop like while ((select * from x ) is not null)
and then delete the first row
I want to do a while loop instead of an cursor...
The statement throws an error and asks for exists.
But if I use exists and the table is empty the condition is not good
can you help me?
10q

Viewing 13 posts - 1 through 12 (of 12 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Please check the links in my signature line for how to post a more complete question.

You do not define why you need to loop and what constitutes the first row.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

newbie_dev SSCommitted Points: 1916 More actions · Answer 2

sorry

I have a parametric table with reports (id int, report_name varchar(30))

For those reports I will get from other table lots of information in a stored procedure in this way : if the report with lowest id doesn't exist in my database (for a person - person table (name, adress,rep_date,report - fk(reports))) , it wasn't created or has a date biger than getdate()-30 , i will take the info for the next one,if this one doesn't exist the next one , and so on.

So the question was, if I send as a parameter to my sp ,for instance the third report,how can I check if it exist and take the report name in the same time.

My first try was to do something like

while (select *

from reports

inner join person on reports.id = person.report

where date> datediff(day,30,getdate ())) is null

to go on the next report

an search it in the same way

I hope this is more clear.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

shnex (1/13/2009)
I hope this is more clear.

Not really. Can you please read the links in Jack's sig.

Why do you want a loop?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

newbie_dev SSCommitted Points: 1916 More actions · Answer 4

because i don't know other way.... I haven't found other solution

I have to take the first raport from that table that respects the conditions...

so, I have to check every one of them until I find the right one...

My first thought is a loop.

Is there a better solution?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

shnex (1/13/2009)
Is there a better solution?

I don't know. There probably is, but you haven't given us enough information to help you.

Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

newbie_dev SSCommitted Points: 1916 More actions · Answer 6

I should be kicked a little for the way I posted 😀

I will write the tables and the desired output

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

shnex (1/13/2009)
I should be kicked a little for the way I posted 😀
I will write the tables and the desired output

🙂 Don't worry. We only kick repeat offenders. 😉

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

newbie_dev SSCommitted Points: 1916 More actions · Answer 8

person

(

person_id,

first_name,

last_name,

adress

)

person_report

(

id,

person_id

report_date,

report_id

)

reports

(

report_id,

report_xml,

report_number

)

report_type

(

report_type_id,

report_name

report_number

)

person:

1 john john xy_adress

2 jane jane yy_adress

person report:

1101.01.20091

2101.01.20094

3201.01.20095

4201.01.20097

reports are like :

1 xml_text 2,

2 xml_text 4,

report_type

1report_x1

2report_y2

3report_z3

and for a person I want to extract the xml for a desired report(report_number)

for instance I send that I want report 3

If the report 3 doesn't exist, or the report is older than 30 days, I have to search for the report 4

the same logic for report 4...and so on

It's more than I said before,because I need mainly an answer and I wil try to solve this.

I don't know how to verify if the reports exists and save it and stop without saving all the reports in a temporary table and then take from there.

that was my first solution.

Is this more clear?:D I hope it is...

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

It would be nicer it it was a script that I could just run to create the tables and insert the data, but it will do. I probably won't be testing any solution though.

How do you tell that a report's older than 30 days? From the person report table? Is it older just for that person (so person1 could have report 1 60 days old, but person2 has report1 10 days old?

What's the link column between report and report type?

What column signifies the report order? (Report number in the reports table?)

As a first guess, try this...

;WITH BestReport (id, RowNo) AS (

SELECT r.report_id, ROW_NUMBER() OVER (ORDER BY report_number) AS RowNo

FROM person_report pr

inner join report r on pr.report_id = p.reportID

WHERE pr.personID = @PersonID -- specify this to limit the person

AND pr.reportdate > DATEADD(dd,-30,getdate())

AND report_id >= @SomeReportID -- this will be the minimum ID that will be returned

)

SELECT id

FROM BestReport

WHERE RowNo = 1

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

j nuñez SSCrazy Points: 2175 More actions · Answer 10

If i have understood what you want, another solution can be:

SELECT MIN (report_id) as rid

FROM person_report pr JOIN reports r ON pr.report_id = r.report_id

WHERE pr.person_id = @pID -- Person identifier

AND pr.report_date > DATEADD(dd, -30, GETDATE())

AND r.report_number >= @pRP -- Report number

This sentence gives you the report_id, then you can use it to return the xml.

newbie_dev SSCommitted Points: 1916 More actions · Answer 11

Thank you for your answers ..I will try to use what you posted, and sorry for the way I posted...

it's the last time....I promise :D:)

J-440512 SSCertifiable Points: 6285 More actions · Answer 12

Don't worry. We only kick repeat offenders.

OUCH! OUCH!! OUCH!!! ...