Dynamic SQL or CASE statement?

hi all,
we are having an asp app that is fetching a big resultset to display in a
list screen. The screen has 4 or 5 columns which have the click sort option
(ie., on clicking the column hdr, the list needs to be sorted out).
Currently, we are using a procedure which would dynamically construct the
sort order and use exec(@.stmt) to send back the resultset. Coz of perf issue
s
of late, we are thinking of removing dyn sql in the proc and recreating it
using CASE/IF statements. Which option would be better in general? Currently
,
the dyn sql has high counts of CPU.
would appreciate any help in this regards
thx
paraaWhy sort it at all? Can't ASP handle that? It would be more scalable than
making SQL Server do all the sorting.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"paraa" <paraa@.discussions.microsoft.com> wrote in message
news:6D3D38EC-AAB8-4BF0-A74F-AAE898D73BBD@.microsoft.com...
hi all,
we are having an asp app that is fetching a big resultset to display in a
list screen. The screen has 4 or 5 columns which have the click sort option
(ie., on clicking the column hdr, the list needs to be sorted out).
Currently, we are using a procedure which would dynamically construct the
sort order and use exec(@.stmt) to send back the resultset. Coz of perf
issues
of late, we are thinking of removing dyn sql in the proc and recreating it
using CASE/IF statements. Which option would be better in general?
Currently,
the dyn sql has high counts of CPU.
would appreciate any help in this regards
thx
paraa|||tom,
we have lean clients and so after lot of brainstorming have put the sorting
on the server side..sorting on client is not an option over here..as per the
scalability factor - may not be that much of an issue..thatz why we are
thinking abt using CASE on the proc side...wanted to know if that would buy
us something vis a vis dyn sql
thx
"Tom Moreau" wrote:

> Why sort it at all? Can't ASP handle that? It would be more scalable tha
n
> making SQL Server do all the sorting.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "paraa" <paraa@.discussions.microsoft.com> wrote in message
> news:6D3D38EC-AAB8-4BF0-A74F-AAE898D73BBD@.microsoft.com...
> hi all,
> we are having an asp app that is fetching a big resultset to display in a
> list screen. The screen has 4 or 5 columns which have the click sort optio
n
> (ie., on clicking the column hdr, the list needs to be sorted out).
> Currently, we are using a procedure which would dynamically construct the
> sort order and use exec(@.stmt) to send back the resultset. Coz of perf
> issues
> of late, we are thinking of removing dyn sql in the proc and recreating it
> using CASE/IF statements. Which option would be better in general?
> Currently,
> the dyn sql has high counts of CPU.
> would appreciate any help in this regards
> thx
> paraa
>|||How "lean" are we talking? I find it hard to believe that an IIS server
can't handle this.
That said, you can do dynamic sorting without resorting to dynamic SQL:
declare @.sort varchar (100)
set @.sort = 'Country'
select
*
from
dbo.Customers
order by
case when @.sort = 'Country' then Country end
, case when @.sort = 'CustomerID' then CustomerID end
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"paraa" <paraa@.discussions.microsoft.com> wrote in message
news:C3B84B96-3839-4A66-8FAE-0F737FBE21C5@.microsoft.com...
tom,
we have lean clients and so after lot of brainstorming have put the sorting
on the server side..sorting on client is not an option over here..as per the
scalability factor - may not be that much of an issue..thatz why we are
thinking abt using CASE on the proc side...wanted to know if that would buy
us something vis a vis dyn sql
thx
"Tom Moreau" wrote:

> Why sort it at all? Can't ASP handle that? It would be more scalable
> than
> making SQL Server do all the sorting.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "paraa" <paraa@.discussions.microsoft.com> wrote in message
> news:6D3D38EC-AAB8-4BF0-A74F-AAE898D73BBD@.microsoft.com...
> hi all,
> we are having an asp app that is fetching a big resultset to display in a
> list screen. The screen has 4 or 5 columns which have the click sort
> option
> (ie., on clicking the column hdr, the list needs to be sorted out).
> Currently, we are using a procedure which would dynamically construct the
> sort order and use exec(@.stmt) to send back the resultset. Coz of perf
> issues
> of late, we are thinking of removing dyn sql in the proc and recreating it
> using CASE/IF statements. Which option would be better in general?
> Currently,
> the dyn sql has high counts of CPU.
> would appreciate any help in this regards
> thx
> paraa
>|||If you simply need to sort by a single column, then the ASP.NET DataGrid
could handle this. However, if your sort requirements are more complex
involving multiple columns or complex conditions, then it would probably
need to be done within the stored procedure. A conditional [order by] clause
using a case expression would be much preferable than dynamic SQL. Just as a
side note, you can also include case expressions in the [where] clause as
well for conditional row filtering, or even in the [group by] clause for
conditional grouping.
In the example below, there are 3 different sort options enabled using a
parameter called @.sort :
. . .
order by
case @.sort
when 0 then NULL
when 1 then company
when 2 then region
end,
case @.sort
when 0 then accountno
when 1 then lastname
when 2 then lastname
end
"paraa" <paraa@.discussions.microsoft.com> wrote in message
news:6D3D38EC-AAB8-4BF0-A74F-AAE898D73BBD@.microsoft.com...
> hi all,
> we are having an asp app that is fetching a big resultset to display in a
> list screen. The screen has 4 or 5 columns which have the click sort
> option
> (ie., on clicking the column hdr, the list needs to be sorted out).
> Currently, we are using a procedure which would dynamically construct the
> sort order and use exec(@.stmt) to send back the resultset. Coz of perf
> issues
> of late, we are thinking of removing dyn sql in the proc and recreating it
> using CASE/IF statements. Which option would be better in general?
> Currently,
> the dyn sql has high counts of CPU.
> would appreciate any help in this regards
> thx
> paraa|||thx a bunch JT and tom..thatz what i was arriving at when i mentioned CASE
stmt v/s dyn sql...just wanted to make sure with the xperts
"JT" wrote:

> If you simply need to sort by a single column, then the ASP.NET DataGrid
> could handle this. However, if your sort requirements are more complex
> involving multiple columns or complex conditions, then it would probably
> need to be done within the stored procedure. A conditional [order by] clau
se
> using a case expression would be much preferable than dynamic SQL. Just as
a
> side note, you can also include case expressions in the [where] clause as
> well for conditional row filtering, or even in the [group by] clause for
> conditional grouping.
> In the example below, there are 3 different sort options enabled using a
> parameter called @.sort :
> .. . .
> order by
> case @.sort
> when 0 then NULL
> when 1 then company
> when 2 then region
> end,
> case @.sort
> when 0 then accountno
> when 1 then lastname
> when 2 then lastname
> end
> "paraa" <paraa@.discussions.microsoft.com> wrote in message
> news:6D3D38EC-AAB8-4BF0-A74F-AAE898D73BBD@.microsoft.com...
>
>