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
