Tuesday, March 20, 2012

Custom sort records in a stored proc

Is there any way to modify this proc so that:
If it recieves a non-negative centre_id the data is ordered by
centre_name but with the centre whose id was passed, being first in
the the list.
If -1 is passed then just order by centre_name.
thanks.
CREATE PROCEDURE [dbo].[get_centres]
@.centre_id smallint
AS
SELECT centre_id, centre_name FROM tbl_centre
ORDER BY CASE
WHEN centre_id= @.centre_id Then 0
WHEN centre_id=-1 THEN centre_name
ELSE centre_id End
GOSELECT centre_id, centre_name
FROM tbl_centre
ORDER BY
CASE WHEN centre_id <> @.centre_id THEN 1 END,
centre_name
David Portas
SQL Server MVP
--|||Try,
...
order by
case when @.centre_id > -1 and centre_id = @.centre_id then 0 else 1 end,
centre_name;
AMB
"hals_left" wrote:

> Is there any way to modify this proc so that:
> If it recieves a non-negative centre_id the data is ordered by
> centre_name but with the centre whose id was passed, being first in
> the the list.
> If -1 is passed then just order by centre_name.
> thanks.
> CREATE PROCEDURE [dbo].[get_centres]
> @.centre_id smallint
> AS
> SELECT centre_id, centre_name FROM tbl_centre
> ORDER BY CASE
> WHEN centre_id= @.centre_id Then 0
> WHEN centre_id=-1 THEN centre_name
> ELSE centre_id End
> GO
>|||Thanks.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<1114789890.6
70395.10780@.g14g2000cwa.googlegroups.com>...
> SELECT centre_id, centre_name
> FROM tbl_centre
> ORDER BY
> CASE WHEN centre_id <> @.centre_id THEN 1 END,
> centre_name

No comments:

Post a Comment