Sort By parameter?

[TomB]

I'd like to sort my stored procedure by the column name I pass in...how
would I do so?


CREATE PROCEDURE dbo.spHelpDeskGetItemsForHistory
(
@SortBy varchar(11),
@SortDirection varchar(4)
)
AS
SET NOCOUNT ON

SELECT tblHelpDeskCategories.DisplayName AS Category,
tblHelpDeskProblems.DateTimeStamp AS DateTime,
tblHelpDeskProblems.BriefDesc AS Description,
tblPeople.FName, tblPeople.LName
FROM tblHelpDeskProblems
INNER JOIN
tblPeople
ON tblHelpDeskProblems.UserID = tblPeople.PersonID
INNER JOIN
tblHelpDeskCategories
ON tblHelpDeskProblems.CategoryID = tblHelpDeskCategories.ID
ORDER BY [@SortBy] @SortDirection

[Bob Barrows]

TomB wrote:

> I'd like to sort my stored procedure by the column name I pass
> in...how would I do so?
> .

One way is to use dynamic sql:
[url]http://www.algonet.se/~sommar/dynamic_sql.html[/url]

Another is to use CASE

>
> CREATE PROCEDURE dbo.spHelpDeskGetItemsForHistory
> (
> @SortBy varchar(11),
> @SortDirection varchar(4)

<snip>

> ORDER BY

CASE WHEN @sortby='DisplayName' AND @SortDirection
='ASC' THEN DisplayName ELSE 0 END ASC,
CASE WHEN @sortby='DisplayName' AND @SortDirection
='DESC' THEN DisplayName ELSE 0 END DESC,
etc.

HTH,
Bob Barrows


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Aaron Bertrand [MVP]]

> > ORDER BY

> CASE WHEN @sortby='DisplayName' AND @SortDirection
> ='ASC' THEN DisplayName ELSE 0 END ASC,
> CASE WHEN @sortby='DisplayName' AND @SortDirection
> ='DESC' THEN DisplayName ELSE 0 END DESC,

In addition, a trick I learned (at least when dealing with positive numeric
columns) is to use:

CASE @sortby
WHEN 'IntColumn' THEN
CASE @sortDirection
WHEN 'ASC' THEN IntColumn
ELSE -IntColumn END
...
END

[Tom B]

Thanks, I thought I was missing something.


"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:um9DWJ$nDHA.1632@TK2MSFTNGP10.phx.gbl...

> TomB wrote:

> > I'd like to sort my stored procedure by the column name I pass
> > in...how would I do so?
> > .

> One way is to use dynamic sql:
> [url]http://www.algonet.se/~sommar/dynamic_sql.html[/url]
>
> Another is to use CASE

> >
> > CREATE PROCEDURE dbo.spHelpDeskGetItemsForHistory
> > (
> > @SortBy varchar(11),
> > @SortDirection varchar(4)

> <snip>

> > ORDER BY

> CASE WHEN @sortby='DisplayName' AND @SortDirection
> ='ASC' THEN DisplayName ELSE 0 END ASC,
> CASE WHEN @sortby='DisplayName' AND @SortDirection
> ='DESC' THEN DisplayName ELSE 0 END DESC,
> etc.
>
> HTH,
> Bob Barrows
>
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>