i am modifying this code from cflib to make it work with sql server but i am using stored procedures and for reports, i am planning to add a code if the records are more than 500, just show 500, so i am trying to use a reuseable function where i can pass the spx to it because our spx are usually called in the cfquery tags as

exec sp_proclist

so trying to make it work in the function so i can pass thatquery to the function and it fetches that many records, not sure it will work that or i need to modify my reports because the reports are quite quite big and too many to count Here is my trying on github https://trycf.com/gist/2dde579d38157ccf83019557ea62a742/lucee5?theme=monokai

You would want to modify the sp to accept a limit and offset. Writing a method to drop rows returned from the sp seams pretty bad performacne wise

But we have many many spx and reports so doing those modifications is very tedious task I know it’s performance impactful but it’s going to be happening when I have to display the results on screen instead of excel

OK I follow.

look at https://cfdocs.org/cfstoredproc , additionally look at QoQ and duplicate.

But we have many many spx and reports so doing those modifications is very tedious task

Two thoughts on this: 1. It's quite possible to think about this vertically rather than horizontally. I suspect there is one proc that is giving you an issue currently. Fix that one. Use it as a testing ground to hone your solution. When another proc becomes a performance issue, then fix that one. Address each proc separately as you need to, rather than thinking "this perceived problem applies to all procs, so I need to fix them all now". Some procs might never become an issue, so it would not be a good usage of your time to update them unnecessarily. 2. Suck it up. Quite often our work is tedious. If you want excitement: yer in the wrong job. The object of the exercise is to deliver the correct solution, and fetching more data than you need from the DB and then trimming it off afterwards is not the correct solution here.

Here is the issue The report when exported to excel is not an issue It’s an feature we want to add which is to limit the data if we display on screen It’s not about excitement, our whole system is build in such a manner that changing sox will have to be changed on multiple environments and they are somewhat connected to other instances of databases which can be more work what we pre assumed So we came up with this small tweak that let’s limit the data on screen for the spx which are being called When I was doing it I found the mysql limit handy on cflib I tried changing to the sql and was having some troubles when getting that piece to work for spx I hope my question makes more sense now

Oh the question made sense. But we have a duty of care to make sure you are asking the right question, as well as answering it. https://xyproblem.info/

I belive what you want it a funtion to drop rows from a query?

@paul yes, because i can use maxrows too in cfquery to limit the rows but in few cases we have cfstoredproc, so i want a seperate function to handle this issue

You can with the correct access just create your stored procedures with MSS or any other SQL tool. Open up MSS, right-click on the stored PROC you need to modify, and choose "Create as" in the "new window" then you have the code to recreate your stored proc. Now go give it a new name, like I_NEED_THIS, then at the very beginning past the GO, USE XYZ, and add SET ROWCOUNT YY; where YY is the number of records you want to return. My suggestion is to read up on transact-sql as this isnt really a ColdFusion issue.

@Evil Ware i know how to do in mssql, but i think i am not looking at sql fix

@gsr like this: https://trycf.com/gist/3b25c51254e0d612550e1a62b519c2e1/lucee5?theme=monokai