March 31, 2009 at 10:03 am
I've been able to handle dynamic sorting with something similar to this in the past.
order by
Case when @SortBy = 'roadway' then roadtobeclosed_Name End,
CAse When @Sortby = 'laneid' then laneclosure_pk End,
Case When @SortBy = 'closuretype' then closuretype_name End,
Case When @SortBy = 'requeststatus' then laneclosure_status_name End,
Case When @SortBy = 'startendmps' then start_milepost End,
CAse When @SortBy = 'startenddate' then date_of_closure End
Ofcourse this works fins as long as your want your sorting Ascending. So I'm trying to find a way to drive the direction in the same manner with a simple case when @SortDir = '' then '' else 'Desc' End. Anyone ever tried something like this?
March 31, 2009 at 10:30 am
I'm sure this isn't the best way.. but you could build your sql into a string and pass the sort type (ASC or DESC) as a parameter and then execute the sql
ie.
CREATE PROCEDURE p_someproc
@sortby VARCHAR(25),
@sorttype VARCHAR(4)
AS
DECLARE @strSP NVARCHAR(1000)
SET @strSP = N'
SELECT * FROM myTable
ORDER BY ' + @sortby + ' ' + @sorttype + ' '
EXEC sp_executesql @strSP
GO
And then call it like:
EXEC p_someproc 'fieldName', 'DESC'
Like I say.. not the nicest way to do things.. should work though?
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
March 31, 2009 at 12:38 pm
Santiago E. Perez (3/31/2009)
I've been able to handle dynamic sorting with something similar to this in the past.order by
Case when @SortBy = 'roadway' then roadtobeclosed_Name End,
CAse When @Sortby = 'laneid' then laneclosure_pk End,
Case When @SortBy = 'closuretype' then closuretype_name End,
Case When @SortBy = 'requeststatus' then laneclosure_status_name End,
Case When @SortBy = 'startendmps' then start_milepost End,
CAse When @SortBy = 'startenddate' then date_of_closure End
Ofcourse this works fins as long as your want your sorting Ascending. So I'm trying to find a way to drive the direction in the same manner with a simple case when @SortDir = '' then '' else 'Desc' End. Anyone ever tried something like this?
Can you post your actual code please? What you have posted does not look right or complete and it does matter in this case.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 31, 2009 at 12:40 pm
torpkev (3/31/2009)
I'm sure this isn't the best way.. but you could build your sql into a string and pass the sort type (ASC or DESC) as a parameter and then execute the sqlie.
CREATE PROCEDURE p_someproc
@sortby VARCHAR(25),
@sorttype VARCHAR(4)
AS
DECLARE @strSP NVARCHAR(1000)
SET @strSP = N'
SELECT * FROM myTable
ORDER BY ' + @sortby + ' ' + @sorttype + ' '
EXEC sp_executesql @strSP
GO
And then call it like:
EXEC p_someproc 'fieldName', 'DESC'
Like I say.. not the nicest way to do things.. should work though?
I would not recommend this as it is a serious injection risk.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 31, 2009 at 1:01 pm
RBarryYoung (3/31/2009)
torpkev (3/31/2009)
I'm sure this isn't the best way.. but you could build your sql into a string and pass the sort type (ASC or DESC) as a parameter and then execute the sqlie.
CREATE PROCEDURE p_someproc
@sortby VARCHAR(25),
@sorttype VARCHAR(4)
AS
DECLARE @strSP NVARCHAR(1000)
SET @strSP = N'
SELECT * FROM myTable
ORDER BY ' + @sortby + ' ' + @sorttype + ' '
EXEC sp_executesql @strSP
GO
And then call it like:
EXEC p_someproc 'fieldName', 'DESC'
Like I say.. not the nicest way to do things.. should work though?
I would not recommend this as it is a serious injection risk.
I agree, not the best method.
maybe try
ALTER PROCEDURE p_someproc
@sorttype VARCHAR(4)
AS
IF @sorttype = 'DESC'
BEGIN
SELECT * FROM myTable
ORDER BY myField DESC
END
ELSE
BEGIN
SELECT * FROM myTable
ORDER BY myField ASC
END
GO
better? I'd be interested to see the reply on this.. it'd help me out.
Thanks
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
March 31, 2009 at 1:07 pm
Yes, that is OK.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 31, 2009 at 1:28 pm
Here's my Proc. I don't think it's necessary to have an if statement with two identical sets of SQL differing only on sort direction.
/***=================================================================================================
TEST UNIT
*****************************************************************************************************
=====================================================================================================
exec get_lcm_laneclosure_Filter @closure_type_id = 2
exec get_lcm_laneclosure_Filter @IncludeInactiveRequestSOurces=1,@IncludeSpecialEvents=0,@RequestSOurcesIDs=N'
',@BeginMP=0,@EndMP=0
=====================================================================================================
ALTER PROCEDURE [dbo].[get_lcm_laneclosure_Filter]
@Laneclosure_IDint = null,
@IncludeInactiveRequestSOurcesbit = null,
@Closure_Type_IDint = null,
@RequestSOurcesIDsXML = null,
@IncludeSpecialEventsbit = null ,
@DaytimeClosuresOnlybit = null,
@Status_IDint = null,
@RoadtobeClosed_IDint = null,
@BeginMPdecimal(6,3) = null ,
@EndMPdecimal(6,3) = null ,
@BICint = null,
@DirectionIDint = null,
@StartDateDateTime = null,
@EndDateDateTime = null,
@DateEnteredStartDateTime = null,
@DateEnteredEndDateTime = null,
@PrimarySortvarchar(50) = 'date_Created',
@PrimarySortDIrectionchar(4) = ''
AS
BEGIN
SET NOCOUNT ON;
SET ARITHABORT ON;
Create Table #ResourceIDs
(requestsource_pk int)
Create Table #laneclosures
(IDint)
Insert into #ResourceIDs
Select T.c.value('RSourceID[1]','int')
From @RequestSOurcesIDs.nodes('/RSources/RSource') as T(c)
Insert into #laneclosures(ID)
SELECT laneclosure_pk
from dbo.[LCM_V_LaneClosureNotices_All] LCN
Left Join dbo.LCM_LaneClosure_DirectionAffected DA on LCN.laneclosure_pk = DA.laneclosure_Fk
Where
LCN.laneclosure_pk = coalesce(Nullif(@Laneclosure_ID,0),LCN.Laneclosure_pk)
and LCN.ClosureType_pk =
case when @IncludeSpecialEvents =1 then 7
else Coalesce(Nullif(@Closure_Type_ID,0),LCN.ClosureType_pk)
End
and LCN.daytime_closure_request = Coalesce(Nullif(@DaytimeClosuresOnly,0),LCN.daytime_closure_request)
and LCN.laneclosure_status_fk = coalesce(Nullif(@Status_ID,0),LCN.laneclosure_status_fk)
and LCN.roadtobeclosed_pk = coalesce(Nullif(@RoadtobeClosed_ID,0),LCN.roadtobeclosed_pk)
and (LCN.Start_Milepost between Coalesce(Nullif(@BeginMP,0),Start_MilePost) and Coalesce(Nullif(@EndMP,0),End_MilePOst))
and (End_MilePOst between Coalesce(nullif(@BeginMP,0),Start_MilePost) and Coalesce(Nullif(@EndMP,0),End_MIlePost))
and ((date_of_closure >= coalesce(@StartDate,date_of_closure)) and (date_of_closure <= coalesce(@EndDate,date_of_closure)))
and ((date_created >= coalesce(@DateEnteredStart,date_created)) and (date_created <= coalesce(@DateEnteredEnd, date_created)))
and DA.laneclosure_direction_fk = coalesce(Nullif(@DirectionID,0),laneclosure_direction_fk)
and Exists
(Select 1 From dbo.LCM_RequestSource RS
Where RS.requestsource_pk = LCN.requestsource_pk
and RS.Active_flag = coalesce(Nullif(@IncludeInactiveRequestSOurces,1),Active_Flag))
if Exists(Select 1 From #ResourceIDs)
BEGIN
Select *
from LCM_V_LaneClosureNotices_Summary LC_S
Inner Join #ResourceIDs R on LC_S.RequestSource_fk = R.requestsource_pk
where laneclosure_pk in (Select Distinct ID from #laneclosures)
Order by
case when @PrimarySort = 'date_Created' then date_Created END ,
case when @PrimarySort = 'date_Createds' then date_Created END
END
else
BEGIN
Select *
from LCM_V_LaneClosureNotices_Summary LC_S
where laneclosure_pk in (Select Distinct ID from #laneclosures)
order by date_Created Desc
END
Drop Table #ResourceIDs
Drop table #laneclosures
END
March 31, 2009 at 1:50 pm
OK, in that case I would recommend two things: first when there are multiple values for the same potential ordering key, they should be combined. And secondly, when you want a key to be descending prefix the parameter with a "-".
So from you example above, you could change this:
Order by
case when @PrimarySort = 'date_Created' then date_Created END ,
case when @PrimarySort = 'date_Createds' then date_Created END
to this:
Order by
case when @PrimarySort = 'date_Created' or @PrimarySort = 'date_Createds'
then date_Created END ASC,
case when @PrimarySort = '-date_Created' or @PrimarySort = '-date_Createds'
then date_Created END DESC,
END
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 31, 2009 at 1:59 pm
Excellent trick! I like it! WIll give it a whirl and see how it works for me. Will report back.
thanks!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply