Two-way Sorting Using Stored Procs...

[Roy]

Greetings,

I've been avoiding it for so long, but like an evil wraith it always
returns to haunt me. The bane of my existence, it is... bidirectional
sorting!!!

Checked out previous posts and none seem to be of assistance to me.

Here's the scoop. I have a web app which populates a sortable, pageable
datagrid using a stored proc. Everything works great, however, I've
been trying on-and-off for 2 months to get the sort to be both asc and
desc. Enough's enough already, there has to be a simpler way than
writing a million "If Then" SQL loops. My SP is below, I'm not posting
any .NET code since I assume it's irrelevant for my purposes as this
bidirectional sorting should occur on SQL Server given my dynamic
sorting.


CREATE PROCEDURE [Get_Edit_Info]
@CurrentPage int,
@PageSize int,
@SortField nvarchar(50),
@scac nvarchar(4),
@emp nvarchar(30),
@poe varchar(3),
@vdn nvarchar(6),
@Dt_Begin nvarchar(10),
@Dt_End nvarchar(10),
@container nvarchar(6),
@booking nvarchar(11),
@TotalRecords Int Output
AS

SET NOCOUNT ON
SET DATEFORMAT mdy

If LEN(@Dt_Begin) < 5 OR LEN(@Dt_Begin) IS NULL
BEGIN
SET @Dt_Begin = '01-01-1900'
END
If LEN(@Dt_End) < 5 OR LEN(@Dt_End) IS NULL
BEGIN
SET @Dt_End = '01-01-2030'
END
If LEN(@poe) < 3
BEGIN
SET @poe = '%'
END
If LEN(@vdn) < 5
BEGIN
SET @vdn = '%'
END
If LEN(@container) < 5
BEGIN
SET @container = '%'
END
If LEN(@booking) < 5
BEGIN
SET @booking = '%'
END
If LEN(@emp) < 2
BEGIN
SET @emp = '%'
END

DECLARE @FirstRec int, @LastRec int
DECLARE @TempTable Table
(
RowNum INTEGER PRIMARY KEY Identity NOT NULL,
van_owner nvarchar (4) NULL,
tcon_container_num nvarchar(8) NULL,
booking_num nvarchar(25) NULL,
poe varchar (3) NULL,
old_poe varchar(3) NULL,
ship_name nvarchar(50) NULL,
old_ship_name nvarchar(50) NULL,
sail_date smalldatetime NULL,
old_sail_date smalldatetime NULL,
voydoc nvarchar(6) NULL,
old_voydoc nvarchar(6) NULL,
employee nvarchar (30) NULL,
update_date smalldatetime NULL
)

INSERT INTO @TempTable
(
van_owner,
tcon_container_num,
booking_num,
poe,
old_poe,
ship_name,
old_ship_name,
sail_date,
old_sail_date,
voydoc,
old_voydoc,
employee,
update_date
)
SELECT
van_owner,
tcon_container_num,
booking_num,
poe,
old_poe,
ship_name,
old_ship_name,
sail_date,
old_sail_date,
voydoc,
old_voydoc,
employee,
update_date
FROM
dbo.MC
WHERE
(id <> 'BB3531CA-024C-499D-AA0C-90D6AC037A70') AND
(isnull(van_owner,'') like @scac) AND
(isnull(tcon_container_num,'') like @container) AND
(isnull(booking_num,'') like @booking) AND
(isnull(voydoc,'') like @vdn) AND
(isnull(poe,'') like ltrim(rtrim(@poe))) AND
(ltrim(rtrim(employee)) like @emp) AND
((update_date BETWEEN CAST(@Dt_Begin as smalldatetime) AND CAST(@Dt_End
as smalldatetime)) OR (update_date IS NULL))
ORDER BY
CASE
WHEN @SortField = 'van_owner' THEN van_owner
WHEN @SortField = 'tcon_container_num' THEN tcon_container_num
WHEN @SortField = 'booking_num' THEN booking_num
WHEN @SortField = 'ship_name' THEN ship_name
WHEN @SortField = 'old_ship_name' THEN old_ship_name
WHEN @SortField = 'voydoc' THEN voydoc
WHEN @SortField = 'old_voydoc' THEN old_voydoc
WHEN @SortField = 'employee' THEN employee

END,
CASE
WHEN @SortField = 'sail_date' THEN sail_date
WHEN @SortField = 'old_sail_date' THEN old_sail_date
END,
CASE
WHEN @SortField = 'poe' THEN poe
WHEN @SortField = 'old_poe' THEN old_poe
END

IF @PageSize = 0
BEGIN
SELECT
van_owner,
tcon_container_num,
booking_num,
poe,
old_poe,
ship_name,
old_ship_name,
sail_date,
old_sail_date,
voydoc,
old_voydoc,
employee,
update_date
FROM @TempTable
RETURN
END
ELSE
BEGIN
SELECT @FirstRec = (@CurrentPage - 1) * @PageSize
SELECT @LastRec = (@CurrentPage * @PageSize + 1)

SELECT
van_owner,
tcon_container_num,
booking_num,
poe,
old_poe,
ship_name,
old_ship_name,
sail_date,
old_sail_date,
voydoc,
old_voydoc,
employee,
update_date
FROM @TempTable
WHERE
RowNum BETWEEN @FirstRec AND @LastRec

SELECT @TotalRecords = COUNT(*) FROM @TempTable
END
GO

 

[souri challa]

Roy,

If you have to do this in a stored procedure a better option may be
to use dynamic sql for the sorting. (only in the last statement, using
it every where would make the code unreadable.)
get your result set into a temp table without any sorting, and your
final statement would look like..
set @sql = 'select * from temptable order by '+@sortfield +' '+
@sortdirection
you need to pass the sort direction from the client ofcourse.
HTH,
-Souri Challa

 

[souri challa]

you need to execute the sql statement also. exec(@sql)
-Souri

 

[Roy]

Souri,

Thanks for the reply, but I've already been down that path... :-(
It doesn't work for me because it only sorts the "page" of data, not
the whole record set. When I try to use dynamic sql with the larger
insert query I garner all manner of errors...

 

[souri challa]

It is still doable. Just include page variable into your dynamic sql.
i.e get all the data into @temptable ( get rid of the order by), create
and execute a different dynamic sql string based on @pagesize and
@currentpage parameters. Just make sure your dynamic sql expression
evalautes to a valid statement.