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_own er,'') like @scac) AND
(isnull(tcon_co ntainer_num,'') like @container) AND
(isnull(booking _num,'') like @booking) AND
(isnull(voydoc, '') like @vdn) AND
(isnull(poe,'') like ltrim(rtrim(@po e))) AND
(ltrim(rtrim(em ployee)) 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 4 1241
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
you need to execute the sql statement also. exec(@sql)
-Souri
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...
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: John Grenier |
last post by:
Hi,
I have to determine the "standing" (WIN - TIE - LOSS) from
confrontations between two teams on a contest. The table matchResults
has fields cont_id, team_id and contest_result (int).
TABLE matchResults
cont_id team_id contest_result
1 1 3
1 2 5
|
by: Willem |
last post by:
Hi,
I have a newbie question: is it possible to make a search form in asp that
searches in two different databases (access)?
Willem
|
by: Mary Jane Pronio |
last post by:
Any information would be greatly appreciated~~!!
Thanks!
|
by: Paul T. RONG |
last post by:
Is it possible to divide a tall subform with 80 records to two subforms each
with 40 records?
Dear All,
What I have: Tables: tblProduct, tblOrder, tblOrderDetail
|
by: Johnny Hu |
last post by:
my program needs to print monthly bills
i want to print bill and bill details on two printers synchronous
how to find the two printers installed on computer and control them to print
together?
regards,
Johnny Hu.
| |
by: Mingle |
last post by:
Hi, all
I am developing a program for displaying two pictures separately in two monitors ( A and B). The goal is: when the mouse pointer is pointed to a picture loaded in picturebox1 in A, a "virtual" mouse pointer is drawn at the same location in picturebox2 in B. The program works well for two monitors with the same size (note: NOT the same resolution), even when I set two different resolutions for them, the mouse pointer locations in the...
|
by: clintonG |
last post by:
I applied aspnet_regsql to SQL2K which was working fine throughout Beta 2
development. After installing Visual Studio and SQL Express RTM my
application has blown up. Logging in to the application became
realllllllllllly slow. Content in LoginView Role Groups was not displaying
even after a user in a role had logged in.
It was taking about 15 seconds or so for the login control to display when
the login link was selected on the homepage....
|
by: Steven |
last post by:
Hello,
I have a question about strcmp(). I have four words, who need to be
compared if it were two strings. I tried adding the comparison values
like '(strcmp(w1, w2) + strcmp(w3, w4))', where w1 and w2 make up the
first string and, w3 and w4 make up the second string. I do not want
to allocate memory, then put the words together to create a string
only to facilitate strcmp() comparison.
My question; Does anyone know how to get the...
|
by: Simon |
last post by:
Dear reader,
For matching of two fields it is required that the fields has the same
content.
But now I have the situation that two text fields has not precisely the same
content but there is some minute deference.
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |