Paging, Soring, Filtering Already-written SPs Result

11 Jan 2007 9:13 AM

Khafancoder

Hi guys,
we developed a large-scale web-application which uses sqlserver 2005,
and we created all of SPs for geting list of particular records or
geting a record info.
i.e Products_GetList | Products_GetInfo & etc.

now we need to implement paging,sorting or filtering on these SPs
results...
& we couldn't do this at webapplication level because of huge number of
records... also it's not possible to change all of these SPs(we have
more than 300 SPs already)

in other words i'm searching for a way to create a generic method(SP or
UDF) which do the paging & other operations on result of all SPs if
required.

i.e :
MyApp --> DAL --> Call Specific SP with Specific Sorting --> DB -->
[Generic SP (do sorting)] --> Specific SP

Thanks

11 Jan 2007 9:45 AM

Uri Dimant

Hi
http://databases.aspfaq.com/database/how-do-i-page-through-a-recordset.html

Show quoteHide quote

"Khafancoder" <khafanco***@gmail.com> wrote in message
news:1168506782.577051.145700@k58g2000hse.googlegroups.com...
> Hi guys,
> we developed a large-scale web-application which uses sqlserver 2005,
> and we created all of SPs for geting list of particular records or
> geting a record info.
> i.e Products_GetList | Products_GetInfo & etc.
>
> now we need to implement paging,sorting or filtering on these SPs
> results...
> & we couldn't do this at webapplication level because of huge number of
> records... also it's not possible to change all of these SPs(we have
> more than 300 SPs already)
>
> in other words i'm searching for a way to create a generic method(SP or
> UDF) which do the paging & other operations on result of all SPs if
> required.
>
> i.e :
> MyApp --> DAL --> Call Specific SP with Specific Sorting --> DB -->
> [Generic SP (do sorting)] --> Specific SP
>
>
> Thanks
>

11 Jan 2007 9:51 AM

Paul Ibison

IMO the solution is to redesign, rather than attempt a workaround. You might
think you could use wrapper procedure calls for each stored proc which
caches the results in a temp table and sorts them but the syntax for this is
"insert into #yourtable exec yourproc", so the temp table definition must
already exist and each wrapper proc will therefore be different. Whichever
way you look at it, this will require widespread changes. The sorted column
will need to be provided, and as you are using paging, the page size and
page number will be provided. So, new parameters will get added to each
stored proc call from the application and each stored proc needs editing.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

11 Jan 2007 10:22 AM

Khafancoder

Thanks,
yes, as u said this wrapper method (i.e for sorting) should get
SortColumnName, Asc|Desc and SP name...

if we ignore the temp-table structure problem, other problems will
solved, for example if all of these SPs return a resultset in a
specific format then the wrapper method could insert them in a generic
temp-table and sort them and finally return them.

in this approach i can call "insert into #generictemptable exec SPName"
dynamically by using execute cmd, but i think it will affect on
application performance...

how about CLR Integration ? could we use it to write the wrapper ?

Paul Ibison wrote:
Show quoteHide quote

> IMO the solution is to redesign, rather than attempt a workaround. You might
> think you could use wrapper procedure calls for each stored proc which
> caches the results in a temp table and sorts them but the syntax for this is
> "insert into #yourtable exec yourproc", so the temp table definition must
> already exist and each wrapper proc will therefore be different. Whichever
> way you look at it, this will require widespread changes. The sorted column
> will need to be provided, and as you are using paging, the page size and
> page number will be provided. So, new parameters will get added to each
> stored proc call from the application and each stored proc needs editing.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .

11 Jan 2007 11:12 AM

Paul Ibison

My understanding of CLR integration is that it is great for iterative tasks
and pattern matching but this is pretty standard SQL so I don't see any
benefit there. In the code-behind you could use some paging code applied to
the recordset as mentioned in URI's link, but this performs badly compared
to a where clause run on the server. Essentially I'd still personally go
down the rewrite route unless your tables are incredibly generic.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Thread options