SQL Server Performance Forum – Threads Archive

Dynamic Sorting of reports

Our reports uses stored procedure datasets, is there a way to dynamically sort the reports? I know you can do it if the dataset is of text type and alter the XML code to pass a sort parameter. How about using a stored procedure? Can we use a parameter based sorting?

---

We use SProcs with Dynamic sorting, by implementing the last parameter passed as the SortBy column.
For this we use a select box of available column names, though the value must be identical to the column name in the Select statement (including table, alias or object names).
Example: dbo.MySProc
@MyName varchar(100)=”,
@SortBy varchar(100)=’a.x’
AS SELECT a.x, a.y, b.z
FROM tblAdmins a LEFT JOIN tblEmps b ON a.i = b.i
WHERE a.x LIKE @MyName
ORDER BY @SortBy
Using the call EXEC MySProc ‘a.y’ it will sort the resultant recordset by coumn y of tblAdmins. Hope this assists, Tony

---

I’ve created two methods based on ‘pubs’ database ‘authors’ table. Method #1 is a stored procedure with ‘order by’ driven by a scalar function which determines 1 of 4 predefined sort orders. Method #2 is a stored procedure which takes 1, 2 or 3 sort order parameters. Both methods are designed to work with varchar datatype. To implement proper numeric sorting (1,2,3,4,5,6,7,8,9,10) you would have to modify by prefixing numeric data types with zero (0) padding (01,02,03,04,05,06,07,08,09,10). btw – I’m running MSSQL 2000 SP3a v8.00.818 —- Method #1
use pubs
go
CREATE FUNCTION dbo.fnSort (@pSortOrder int =1 , @pVal1 varchar(255) , @pVal2 varchar(255) )
RETURNS char(512) AS
BEGIN
declare @strSort char(512)
select @strSort = case @pSortOrder
when ‘1’ then @pVal1 + REPLICATE( ‘ ‘, 255 – LEN( @pVal1))
when ‘2’ then @pval2 + REPLICATE( ‘ ‘, 255 – LEN( @pVal2))
when ‘3’ then @pval1 + REPLICATE( ‘ ‘, 255 – LEN( @pVal1)) + @pval2 + REPLICATE( ‘ ‘, 255 – LEN( @pVal2))
when ‘4’ then @pval2 + REPLICATE( ‘ ‘, 255 – LEN( @pVal2)) + @pval1 + REPLICATE( ‘ ‘, 255 – LEN( @pVal1))
end
RETURN @strSort
END go create proc spFoo (@sort int =1)
as
begin
select au_id,au_lname,au_fname,phone,address,city,state,zip,contract from authors
order by dbo.fnsort(@sort, au_lname, au_fname)
end go exec spFoo — sort by au_lname
exec spFoo 2 — sort by au_fname
exec spFoo 3 — sort by au_lname, au_fname
exec spFoo 4 — sort by au_fname, au_lname —- Method #2
use pubs
go
create proc spFoo2 (@sort1 varchar(2)=’1′
,@sort2 varchar(2)=’0′
,@sort3 varchar(2)=’0′
)
as
begin
select au_id,au_lname,au_fname,phone,address,city,state,zip,contract
from authors
order by (case @sort1
when ‘1’ then au_id + REPLICATE( ‘ ‘, 255 – LEN( au_id))
when ‘2’ then au_lname + REPLICATE( ‘ ‘, 255 – LEN( au_lname))
when ‘3’ then au_fname + REPLICATE( ‘ ‘, 255 – LEN( au_fname ))
else REPLICATE( ‘ ‘, 255)
end) +
(case @sort2
when ‘1’ then au_id + REPLICATE( ‘ ‘, 255 – LEN( au_id))
when ‘2’ then au_lname + REPLICATE( ‘ ‘, 255 – LEN( au_lname))
when ‘3’ then au_fname + REPLICATE( ‘ ‘, 255 – LEN( au_fname ))
else REPLICATE( ‘ ‘, 255)
end )+
(case @sort3
when ‘1’ then au_id + REPLICATE( ‘ ‘, 255 – LEN( au_id))
when ‘2’ then au_lname + REPLICATE( ‘ ‘, 255 – LEN( au_lname))
when ‘3’ then au_fname + REPLICATE( ‘ ‘, 255 – LEN( au_fname ))
else REPLICATE( ‘ ‘, 255)
end) end go exec spfoo2 — sort by au_id only
exec spfoo2 ‘2’,’3′ — sort by au_lname, au_fname
exec spfoo2 ‘3’,’2′ — sort by au_fname, au_lname
PS: LogicalMan, I could not get your example to run. I receive the following error:
‘The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.’

---

]]>