Order By in Strore Prod

I want to order by a table dynamicly base on user criteria. If user ckick on gross, the table sort by gross. Below is my strore prod. The table is displayed but when user click on gross, the data remain same, in other words, it not sorted! Can anybody hel
p?
CREATE PROCEDURE SP_GetCorpFinalReport
(
@.SortOrder nvarchar
)
AS
SELECT * FROM CorpFinalReport Order By
CASE @.SortOrder
WHEN 'custno' THEN custno
WHEN 'gross' THEN gross
WHEN 'net' THEN net
WHEN 'npv' THEN npv
WHEN 'plimpact' THEN plimpact
--else RecNo
end DESC
GO
*****************************************
* This message was posted via http://www.sqlmonster.com
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse...de59ba32b430c3
*****************************************
some response please!
*****************************************
* A copy of the whole thread can be found at:
* http://www.sqlmonster.com/Uwe/Forum...l-server/17779
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse...680a157c0dfad7
*****************************************
|||Gabe,
First of all, be sure to change the declaration of @.SortOrder, because
it only holds 1 character now nvarchar is the same as nvarchar(1).
Sysname or nvarchar(bigger-number) is ok.
Then try something like this:
create procedure yourProcedure (
@.SortOrder sysname
) as
select * from CorpFinalReport
order by
case when @.SortOrder = 'custno' then custno end desc,
case when @.SortOrder = 'gross' then gross end desc,
case when @.SortOrder = 'net' then net end desc,
case when @.SortOrder = 'npv' then npv end desc,
case when @.SortOrder = 'plimpact' then plimpact end desc,
RecNo desc
Also, it is not a good idea to name your stored procedure SP_...,
because names that begin with sp_ have owner/db resolution rules that
are designed for system stored procedures.
Steve Kass
Drew University
Gabe Wong via SQLMonster.com wrote:

>I want to order by a table dynamicly base on user criteria. If user ckick on gross, the table sort by gross. Below is my strore prod. The table is displayed but when user click on gross, the data remain same, in other words, it not sorted! Can anybody he
lp?
>CREATE PROCEDURE SP_GetCorpFinalReport
>(
>@.SortOrder nvarchar
>)
>AS
>SELECT * FROM CorpFinalReport Order By
>CASE @.SortOrder
>WHEN 'custno' THEN custno
> WHEN 'gross' THEN gross
> WHEN 'net' THEN net
>WHEN 'npv' THEN npv
>WHEN 'plimpact' THEN plimpact
>--else RecNo
>end DESC
>GO
>*****************************************
>* This message was posted via http://www.sqlmonster.com
>*
>* Report spam or abuse by clicking the following URL:
>* http://www.sqlmonster.com/Uwe/Abuse...de59ba32b430c3
>*****************************************
>
|||You are going to have to handle that by executing a SQL string like:
DECLARE @.SQL VARCHAR(1000)
SELECT @.SQL='SELECT * FROM CorpFinalRport ORDER BY '+@.SortOrder
EXEC(@.SQL)
"Gabe Wong via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:c7db6820f2c6415584de59ba32b430c3@.SQLMonster.c om...
> I want to order by a table dynamicly base on user criteria. If user ckick
on gross, the table sort by gross. Below is my strore prod. The table is
displayed but when user click on gross, the data remain same, in other
words, it not sorted! Can anybody help?
> CREATE PROCEDURE SP_GetCorpFinalReport
> (
> @.SortOrder nvarchar
> )
> AS
> SELECT * FROM CorpFinalReport Order By
> CASE @.SortOrder
> WHEN 'custno' THEN custno
> WHEN 'gross' THEN gross
> WHEN 'net' THEN net
> WHEN 'npv' THEN npv
> WHEN 'plimpact' THEN plimpact
> --else RecNo
> end DESC
> GO
> *****************************************
> * This message was posted via http://www.sqlmonster.com
> *
> * Report spam or abuse by clicking the following URL:
> *
http://www.sqlmonster.com/Uwe/Abuse...de59ba32b430c3
> *****************************************
|||It worl find now, but one more thing, it order by Gross value, the value order by the first char only, why? for example i have 3 value
132
222
1122
now it order by
222
132
1122
but what i want is the biggest come first.
is it i should write : order by max(groos)?
*****************************************
* A copy of the whole thread can be found at:
* http://www.sqlmonster.com/Uwe/Forum...l-server/17779
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse...b0c07e0de1186a
*****************************************
|||What datatype is Gross of in your table?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gabe Wong via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:2b1b743f2fd34951a7b0c07e0de1186a@.SQLMonster.c om...
> It worl find now, but one more thing, it order by Gross value, the value order by the first char
only, why? for example i have 3 value
> 132
> 222
> 1122
> now it order by
> 222
> 132
> 1122
> but what i want is the biggest come first.
> is it i should write : order by max(groos)?
> *****************************************
> * A copy of the whole thread can be found at:
> * http://www.sqlmonster.com/Uwe/Forum...l-server/17779
> *
> * Report spam or abuse by clicking the following URL:
> * http://www.sqlmonster.com/Uwe/Abuse...b0c07e0de1186a
> *****************************************
|||i CAST it to int, now it work perfectly, Thanks ALL!!! if posible come to Malaysia, i buy u dinner!
*****************************************
* A copy of the whole thread can be found at:
* http://www.sqlmonster.com/Uwe/Forum...l-server/17779
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse...bba0c949a38069
*****************************************
|||> some response please!
Geez, have patience. You did post well outside of business hours in the
western hemisphere...
sql