How do I loop through rows? 1

[tpasters]

Using pubs as an example -

Considering the batch below, how would I loop through the 2nd select statement for the number of rows that are selected in the first select statement? Current of course it returns only the last string.

The point of this is to return a short string from each of the rows where the word and is found.

--------- START ---------
declare @i int, @PWORD varchar(30), @PID varchar(10), @PRETURN varchar(50)
set @PWORD='and'
SELECT @PID=title_id, @i=PATINDEX('%and%', notes)
FROM titles where PATINDEX('%and%', notes)>0

select @PRETURN=substring(notes, (@i-25), (@i+24))
from titles where title_id=@PID

print @PWORD
print @PID
print @PRETURN

GO
--------- END ---------

 

[MeanGreen]

Here you go:

select title_id + ' and ' + substring(notes, (PATINDEX('%and%', notes)-25), (PATINDEX('%and%', notes)+24))
FROM titles where PATINDEX('%and%', notes)>0

Hope this helps.

 

[tpasters]

No, I must have not been clear enough.

select statement 1 finds 10+- rows, I need to iterate through select statment #2 10+- times.

 

[MeanGreen]

Run the query and you will get multiple outputs. Only other thought is use a CURSOR to loop... But I hate cursors. I really think this will get you the same results for all findings. Maybe I am just not seeing what you are talking about. Are you wanting to apply every find in query 1 to append every find in query 2.. ie. Row 1 of Query 2 will append rows 1 to 10 of query 1 , then row 2 of query 2 will append rows 1 to 10 of query 1, etc. ?

Can you run the query I provided and explain where the output is incorrect for what you are looking for?

Thank you.

 

[tpasters]

I apologize, I think I was trying to make it harder on myself then I had to, this is great.

Thanks