December 1, 2004 at 5:09 am
I've got a few reports that are run using stored procedures called from a web page. The user can run a report and order the returned rows by a single column OR by a combination of colums, either ascending or descending. So taking the Employees table in Northwind as an example, Report1 can be run to return the rows ordered by any one of or any combination of LastName, Title and/or BirthDate, ASC/DESC.
So in my stored procedure I do my processing of data and at the end do a Select statement to return the results. I need to order these according to what the user has chosen. However, I don't want to use dynamic SQL as it looks dreadful and is difficult to maintain, especially as some of my reports have even more order column options (and multiple combinations) for the user. The way I am currently looking at is below, but any other/better way is most welcome. Also, if anyone knows how to incorporate the ASC/DESC into a single SELECT i.e. without using the IF/ELSE I'd be grateful. Thanks.
--these would be parameters passed into the stored procedure
--by the web-page indicating what columns to order by and in what
--direction: ASC or DESC
DECLARE @OrdBy1 varchar(7)
DECLARE @OrdBy2 varchar(7)
DECLARE @OrdBy3 varchar(7)
DECLARE @AscDesc varchar(7)
--In this example I want the rows ordered by Title; within that by
--Surname; within that by DOB; I want them ordered descending
SET @OrdBy1 = 'Title'
SET @OrdBy2 = 'Surname'
SET @OrdBy3 = 'DOB'
SET @AscDesc = 'DESC'
IF @AscDesc = 'ASC'
SELECT
*
FROM
Employees
ORDER BY
CASE @OrdBy1
WHEN 'Surname' then cast(LastName as sql_variant)
WHEN 'Title' then cast(Title as sql_variant)
WHEN 'DOB' then cast(BirthDate as sql_variant)
END ASC,
CASE @OrdBy2
WHEN 'Title' then cast(Title as sql_variant)
WHEN 'Surname' then cast(LastName as sql_variant)
WHEN 'DOB' then cast(BirthDate as sql_variant)
END ASC,
CASE @OrdBy3
WHEN 'DOB' then cast(BirthDate as sql_variant)
WHEN 'Surname' then cast(LastName as sql_variant)
WHEN 'Title' then cast(Title as sql_variant)
END ASC
ELSE
SELECT
*
FROM
Employees
ORDER BY
CASE @OrdBy1
WHEN 'Surname' then cast(LastName as sql_variant)
WHEN 'Title' then cast(Title as sql_variant)
WHEN 'DOB' then cast(BirthDate as sql_variant)
END DESC,
CASE @OrdBy2
WHEN 'Title' then cast(Title as sql_variant)
WHEN 'Surname' then cast(LastName as sql_variant)
WHEN 'DOB' then cast(BirthDate as sql_variant)
END DESC,
CASE @OrdBy3
WHEN 'DOB' then cast(BirthDate as sql_variant)
WHEN 'Surname' then cast(LastName as sql_variant)
WHEN 'Title' then cast(Title as sql_variant)
END DESC
December 1, 2004 at 5:32 am
I'm no fan of dynamic sql, but in such cases it in superior to static sql. Have a look here http://www.sommarskog.se/dyn-search.html
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply