PASSING ORDER BY AS PARAMETERS

I have a stored procedure that excepts @.orderby as a parameter
and executes following sql statement:
select * from titles
order by
case when @.orderby = 'title' then
title
end
asc
Is there anyway I can add second parameter @.direction and control order
ASC/DESC based on parameter value , like "case when @.direction then asc"?
Thanks in advance
Programmerhttp://www.aspfaq.com/2501
"Sergey Zuyev" <SergeyZuyev@.discussions.microsoft.com> wrote in message
news:10275CAC-EA65-4A76-B4AA-AB873A85DCCF@.microsoft.com...
>I have a stored procedure that excepts @.orderby as a parameter
> and executes following sql statement:
> select * from titles
> order by
> case when @.orderby = 'title' then
> title
> end
> asc
> Is there anyway I can add second parameter @.direction and control order
> ASC/DESC based on parameter value , like "case when @.direction then asc"?
> Thanks in advance
>
> --
> Programmer|||You can use:
select * from titles
order by
case when @.col = 'title' and @.dir = 'asc' then title end,
case when @.col = 'title' and @.dir = 'desc' then title end desc,
case when @.col = 'price' and @.dir = 'asc' then price end,
case when @.col = 'price' and @.dir = 'desc' then price end desc,
..
However, the plan for such a query would result in a table scan plus a sort
operation.
If you want to allow an efficient plan based on the input, you should either
use dynamic execution, which has it's obvious limitations (SQL injection
attacks and other security issues), or the following static approach:
if @.col = 'title' and @.dir = 'asc'
select * from titles order by title
else if @.col = 'title' and @.dir = 'desc'
select * from titles order by title desc
else if @.col = 'price' and @.dir = 'asc'
select * from titles order by price
else if @.col = 'price' and @.dir = 'desc'
select * from titles order by price desc
...
BG, SQL Server MVP
www.SolidQualityLearning.com
"Sergey Zuyev" <SergeyZuyev@.discussions.microsoft.com> wrote in message
news:10275CAC-EA65-4A76-B4AA-AB873A85DCCF@.microsoft.com...
>I have a stored procedure that excepts @.orderby as a parameter
> and executes following sql statement:
> select * from titles
> order by
> case when @.orderby = 'title' then
> title
> end
> asc
> Is there anyway I can add second parameter @.direction and control order
> ASC/DESC based on parameter value , like "case when @.direction then asc"?
> Thanks in advance
>
> --
> Programmer|||Hi
You can try as
exec ('select * from titles order by ' + @.orderby + ' ' + @.order)
please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Sergey Zuyev" wrote:

> I have a stored procedure that excepts @.orderby as a parameter
> and executes following sql statement:
> select * from titles
> order by
> case when @.orderby = 'title' then
> title
> end
> asc
> Is there anyway I can add second parameter @.direction and control order
> ASC/DESC based on parameter value , like "case when @.direction then asc"?
> Thanks in advance
>
> --
> Programmer|||It's not quite that simple.
Create Procedure MyProcedure
@.OrderBy nvarchar(12)
AS
Select * /*We really should list the field names, but this is an
example*/ ,
OrderMeBy = Case @.OrderBy
When 'title' Then title
When 'Price_Asc' Then cast(Price as nvarchar(20))
When 'Price_Desc' Then cast(Price *-1 as nvarchar(20)) /*negated
to go in descending order*/
When 'PubDate_Asc' Then cast( PubDate as nvarchar(20))
When 'PubDate_Desc' Then cast (DateDiff(d, PubDate,
'12/31/2099') as nvarchar(20)) /*subtracted from the future -- gives more
recent dates a lower value, thus descending*/
END
From Titles
Order By OrderMeBy
What I wrote actually has a few sorting problems due to the CAST's, but it
demonstrates the method. I don't have a nice little formula for turning
text around to make it sort backwards like I do for numeric fields and
dates.
hth,
Daniel Wilson
Senior Software Solutions Developer
Embtrak Development Team
http://www.Embtrak.com
DVBrown Company
"Sergey Zuyev" <SergeyZuyev@.discussions.microsoft.com> wrote in message
news:10275CAC-EA65-4A76-B4AA-AB873A85DCCF@.microsoft.com...
> I have a stored procedure that excepts @.orderby as a parameter
> and executes following sql statement:
> select * from titles
> order by
> case when @.orderby = 'title' then
> title
> end
> asc
> Is there anyway I can add second parameter @.direction and control order
> ASC/DESC based on parameter value , like "case when @.direction then asc"?
> Thanks in advance
>
> --
> Programmer|||And what if the user specifies "delete from titles" as the value of @.order ?
"Chandra" <chandra@.discussions.microsoft.com> wrote in message
news:2A5B494C-31B9-4D1E-9AF4-4365C2AF6147@.microsoft.com...
> Hi
> You can try as
> exec ('select * from titles order by ' + @.orderby + ' ' + @.order)
> please let me know if u have any questions
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "Sergey Zuyev" wrote:
>|||Daniel, using multiple CASE expressions instead of one, where each deals
with one column only, solves all the issues related to differences in
datatypes.
Check out the solution I proposed. You will also find a treatment for the
direction issue.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Daniel Wilson" <d.wilson@.embtrak.com> wrote in message
news:umfPT5OnFHA.3256@.TK2MSFTNGP12.phx.gbl...
> It's not quite that simple.
> Create Procedure MyProcedure
> @.OrderBy nvarchar(12)
> AS
> Select * /*We really should list the field names, but this is an
> example*/ ,
> OrderMeBy = Case @.OrderBy
> When 'title' Then title
> When 'Price_Asc' Then cast(Price as nvarchar(20))
> When 'Price_Desc' Then cast(Price *-1 as nvarchar(20))
> /*negated
> to go in descending order*/
> When 'PubDate_Asc' Then cast( PubDate as nvarchar(20))
> When 'PubDate_Desc' Then cast (DateDiff(d, PubDate,
> '12/31/2099') as nvarchar(20)) /*subtracted from the future -- gives more
> recent dates a lower value, thus descending*/
> END
> From Titles
> Order By OrderMeBy
>
> What I wrote actually has a few sorting problems due to the CAST's, but it
> demonstrates the method. I don't have a nice little formula for turning
> text around to make it sort backwards like I do for numeric fields and
> dates.
> hth,
> --
> Daniel Wilson
> Senior Software Solutions Developer
> Embtrak Development Team
> http://www.Embtrak.com
> DVBrown Company
>
> "Sergey Zuyev" <SergeyZuyev@.discussions.microsoft.com> wrote in message
> news:10275CAC-EA65-4A76-B4AA-AB873A85DCCF@.microsoft.com...
>|||well if u specify the lenght to be 4, then the string would not be accepted
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"JT" wrote:

> And what if the user specifies "delete from titles" as the value of @.order
?
> "Chandra" <chandra@.discussions.microsoft.com> wrote in message
> news:2A5B494C-31B9-4D1E-9AF4-4365C2AF6147@.microsoft.com...
>
>|||Thanks, Itzik.
I saw your solution after I'd posted -- I got the basics of my solution here
on this group about 4 years ago. Perhaps SQL Server 7 did not support that
syntax?
Regardless, your solution is much cleaner & I will be putting it to work in
the future.
dwlison
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:%235n$t8OnFHA.1204@.TK2MSFTNGP12.phx.gbl...
> Daniel, using multiple CASE expressions instead of one, where each deals
> with one column only, solves all the issues related to differences in
> datatypes.
> Check out the solution I proposed. You will also find a treatment for the
> direction issue.
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Daniel Wilson" <d.wilson@.embtrak.com> wrote in message
> news:umfPT5OnFHA.3256@.TK2MSFTNGP12.phx.gbl...
more
it
asc"?
>|||> I don't have a nice little formula for turning
> text around to make it sort backwards
Well, the other solutions are better, and this is far from efficient, but it
can be done. This assumes a case insensitive collation.
CREATE FUNCTION dbo.SortReverse
( @.in VARCHAR(32) )
RETURNS VARCHAR(32)
AS
BEGIN
DECLARE @.i TINYINT, @.out VARCHAR(32)
SELECT @.i = 1, @.out = ''
WHILE @.i <= len(@.in)
BEGIN
SELECT @.out = @.out + CHAR(90+65-ASCII(UPPER(SUBSTRING(@.in, @.i, 1))))
SET @.i = @.i + 1
END
RETURN @.out
END
GO
Now you can do this:
DECLARE @.sortOrder VARCHAR(4)
SET @.sortOrder = 'ASC'
SELECT
name,
dbo.SortReverse(name)
FROM sysobjects
ORDER BY CASE @.sortOrder
WHEN 'ASC' THEN name
ELSE dbo.SortReverse(name)
END
SET @.sortOrder = 'DESC'
SELECT
name,
dbo.SortReverse(name)
FROM sysobjects
ORDER BY CASE @.sortOrder
WHEN 'ASC' THEN name
ELSE dbo.SortReverse(name)
END