# SQL Looping when result is a lot of lines

**URL:** <https://clarionhub.com/t/sql-looping-when-result-is-a-lot-of-lines/3467>\
**Category:** questions\
**Created:** [August 7, 2020, 7:13am UTC](https://clarionhub.com/t/sql-looping-when-result-is-a-lot-of-lines/3467 "2020-08-07T07:13:52Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![blue](https://clarionhub.com/letter_avatar_proxy/v4/letter/b/3bc359/32.png) [@blue](https://clarionhub.com/u/blue)\
**Post date:** [August 7, 2020, 7:13am UTC](https://clarionhub.com/t/sql-looping-when-result-is-a-lot-of-lines/3467/1 "2020-08-07T07:13:52Z")

</div>

Hello,

I have question about getting results from SQL in efficient way. I have created `VIEW`, and use `PROP:SQL` to send SQL Select statement.

`View:Partner{PROP:SQL} = 'SELECT SUM(Amount) Amount' & |`  
`' ,[dbo].[Debt].[Partner]' & |`  
`' ,[dbo].[Company].[Naziv]' & |`  
`' FROM [dbo].[Debt] INNER JOIN [dbo].[Company]' & |`  
`' ON [dbo].[Company].IDPartner = [dbo].[Debt].Partner' & |`  
` ' WHERE Type = 1 AND [dbo].[Debt].Company = ' & Company_ID & |`  
`' GROUP BY [dbo].[Debt].Partner, Name' & |`  
`' ORDER BY Name'`

And result is sometimes couple thousand lines. In help it is stated that I should loop thru `VIEW` with `NEXT()`, but when there is a lot lines it takes a time.

Is there any other way to LOOP faster-efficient way thru results?

Clarion APP generator creates:

`BRW1 CLASS(BrowseClass)`  
`Q &Queue:Browse:1`  
`Init PROCEDURE(SIGNED ListBox,*STRING Posit,VIEW V,QUEUE Q,RelationManager RM,WindowManager WM)`

Which is very efficient. It does not get all the results, instead it goes line by line when it is needed.

Is there any way that I can apply my `VIEW{SQL:PROP}` to `INIT()` procedure? I have tried to look at the `abbrowse.clw`, but I could not figured it out.

Procedure `INIT()` is from line 629 to 651 in `abbrowse.clw`, and I could not figure it out where to go from that…

---

<div class="post-metadata">

**Author:** ![Clarion\_clarion](https://clarionhub.com/user_avatar/clarionhub.com/clarion_clarion/32/636_2.png) [@Clarion\_clarion](https://clarionhub.com/u/Clarion_clarion)\
**Post date:** [August 7, 2020, 12:39pm UTC](https://clarionhub.com/t/sql-looping-when-result-is-a-lot-of-lines/3467/2 "2020-08-07T12:39:46Z")

</div>

On SQL side create a View with you view 🙂 In Clarion, DCT - create Table that points to your sql view. APP - use your new table in browse!

---

<div class="post-metadata">

**Author:** ![Rick\_UpperPark](https://clarionhub.com/user_avatar/clarionhub.com/rick_upperpark/32/346_2.png) [@Rick\_UpperPark](https://clarionhub.com/u/Rick_UpperPark)\
**Post date:** [August 7, 2020, 7:28pm UTC](https://clarionhub.com/t/sql-looping-when-result-is-a-lot-of-lines/3467/3 "2020-08-07T19:28:42Z")

</div>

Also, take a look at the BUFFER() command

---

<div class="post-metadata">

**Author:** ![seanh](https://clarionhub.com/letter_avatar_proxy/v4/letter/s/4af34b/32.png) [@seanh](https://clarionhub.com/u/seanh)\
**Post date:** [August 7, 2020, 10:17pm UTC](https://clarionhub.com/t/sql-looping-when-result-is-a-lot-of-lines/3467/4 "2020-08-07T22:17:41Z")

</div>

The SQL that Clarion creates is usually pretty good. Unless you have a particularly odd query, I’d just let the browse do it’s thing. It creates a Cursor and a buffer and is pretty efficient.

---

<div class="post-metadata">

**Author:** ![blue](https://clarionhub.com/letter_avatar_proxy/v4/letter/b/3bc359/32.png) [@blue](https://clarionhub.com/u/blue)\
**Post date:** [August 11, 2020, 10:47am UTC](https://clarionhub.com/t/sql-looping-when-result-is-a-lot-of-lines/3467/5 "2020-08-11T10:47:40Z")

</div>

Thanks for suggestions. I will try with SQL View.

---

<div class="post-metadata">

**Author:** ![Dennis](https://clarionhub.com/letter_avatar_proxy/v4/letter/d/e56c9b/32.png) [@Dennis](https://clarionhub.com/u/Dennis)\
**Post date:** [August 18, 2020, 10:30am UTC](https://clarionhub.com/t/sql-looping-when-result-is-a-lot-of-lines/3467/6 "2020-08-18T10:30:36Z")

</div>

best to use the buffer command as noted. a view on the back end is not going make much difference. it MS SQL Serve the buffer statement will make a significant difference, if set correctly then it will make far less round trips to the server.

as far as this " Is there any way that I can apply my `VIEW{SQL:PROP}`"  
never tried but you can overload next, previous, and a couple others and load a browse or what ever from stored procedure