May 31, 2010 at 11:30 pm
Hi ! All
I need your help regarding a new problem created in one of my stored procedure, The thing is I have to add a variable in that , but I don't know how to add.Exactly I am sending a stored procedure to you. What I have done is I have declare the variable -> @LeadTime AS REAL . Now I have to make a condition in the query that if @LeadTime=RS.LeadTime_Max then there will be sorting in descending order and if the condition is like @LeadTime=RS.LeadTime_Min then the sort will be in ascending order.Now for your information RS.LeadTime_Max and RS.LeadTime_Min are two attribute col that is in the table. I am supplying the stored Procedure beneath , please check and please suggest me necessary changes(to add the condition with the variable).
USE IRISDW_AF ;
IF OBJECT_ID('Proc_LowestHighest_LeadTimes') IS NOT NULL
DROP PROC Proc_LowestHighest_LeadTimes;
GO
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:Subhro Mukherjee
-- Create date: 05/31/10
-- Description: Proc_LowestHighest_LeadTimes
-- =============================================
CREATE PROC Proc_LowestHighest_LeadTimes
@LeadTime AS REAL ------> Variable Declared
AS
SET NOCOUNT ON;
GO
BEGIN
SELECT DISTINCT TOP(20) PM.Partner_Name,PL1.Location as Location_From,PL2.Location as Location_To,RS.Route_Key,RS.LeadTime_Min,RS.LeadTime_Max,CN.Begin_Journey_Date AS Start_Date,
CN.End_Journey_Date AS End_Date,RLH.Container_Key,RLH.Container_Id,RLH.Journey_Arrival_LeadTime
FROM dbo.Partner_Master PM
INNER JOIN dbo.Containers_Ended_Journey CN ON
CN.Enterprise_Partner_Key = PM.Partner_Key
INNER JOIN dbo.Route_Summary RS ON
CN.Enterprise_Partner_Key = RS.Enterprise_Partner_Key
INNER JOIN dbo.Partner_Locations PL1 ON
PL1.Partner_Location_Key = RS.ShipFrom_Partner_Location_Key
INNER JOIN dbo.Partner_Locations PL2 ON
PL2.Partner_Location_Key = RS.ShipTo_Partner_Location_Key
INNER JOIN dbo.Route_LeadTime_History RLH ON
RLH.Route_Key = RS.Route_Key
END
Thanks
Subhro
May 31, 2010 at 11:38 pm
Suhbro, try this:
SELECT DISTINCT TOP(20) PM.Partner_Name,PL1.Location as Location_From,PL2.Location as Location_To,RS.Route_Key,RS.LeadTime_Min,RS.LeadTime_Max,CN.Begin_Journey_Date AS Start_Date,
CN.End_Journey_Date AS End_Date,RLH.Container_Key,RLH.Container_Id,RLH.Journey_Arrival_LeadTime
FROM dbo.Partner_Master PM
INNER JOIN dbo.Containers_Ended_Journey CN ON
CN.Enterprise_Partner_Key = PM.Partner_Key
INNER JOIN dbo.Route_Summary RS ON
CN.Enterprise_Partner_Key = RS.Enterprise_Partner_Key
INNER JOIN dbo.Partner_Locations PL1 ON
PL1.Partner_Location_Key = RS.ShipFrom_Partner_Location_Key
INNER JOIN dbo.Partner_Locations PL2 ON
PL2.Partner_Location_Key = RS.ShipTo_Partner_Location_Key
INNER JOIN dbo.Route_LeadTime_History RLH ON
RLH.Route_Key = RS.Route_Key
-- CASE in ORDER BY clause
ORDER BY
CASE WHEN @LeadTime = RS.LeadTime_Max THEN RS.LeadTime_Max
WHEN @LeadTime = RS.LeadTime_Min THEN RS.LeadTime_Min
END
Am not before my SSMS, so i havent parsed the code, so please tell me if it worked well for you!
June 1, 2010 at 1:41 am
Thanks ColdCoffee for your reply ; but I have thought it in another way, I now have passed two value 'Highest' and 'Lowest' in a variable and putting it in the CASE statements ,
The thing is like this
USE IRISDW_AF ;
IF OBJECT_ID('Proc_LowestHighest_LeadTimes') IS NOT NULL
DROP PROC Proc_LowestHighest_LeadTimes;
GO
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:Subhro Mukherjee
-- Create date: 05/31/10
-- Description: Proc_LowestHighest_LeadTimes
-- =============================================
CREATE PROC Proc_LowestHighest_LeadTimes
@Orderby Varchar(50)
@Num
AS
SET NOCOUNT ON;
BEGIN
SELECT TOP()RLH.Journey_Total_LeadTime,PM.Partner_Name,PL1.Location as Location_From,PL2.Location as Location_To,
RS.Route_Key,RS.LeadTime_Min,RS.LeadTime_Max,CN.Begin_Journey_Date AS Start_Date,
CN.End_Journey_Date AS End_Date,RLH.Container_Key,RLH.Container_Id,RLH.Journey_Arrival_LeadTime
FROM dbo.Partner_Master PM
INNER JOIN dbo.Containers_Ended_Journey CN ON
CN.Enterprise_Partner_Key = PM.Partner_Key
INNER JOIN dbo.Route_Summary RS ON
CN.Enterprise_Partner_Key = RS.Enterprise_Partner_Key
INNER JOIN dbo.Partner_Locations PL1 ON
PL1.Partner_Location_Key = RS.ShipFrom_Partner_Location_Key
INNER JOIN dbo.Partner_Locations PL2 ON
PL2.Partner_Location_Key = RS.ShipTo_Partner_Location_Key
INNER JOIN dbo.Route_LeadTime_History RLH ON
RLH.Route_Key = RS.Route_Key
ORDER BY CASE
WHEN @Orderby = 'Lowest' THEN RLH.Journey_Total_LeadTime
END ASC,
CASE
WHEN @Orderby = 'Highest' THEN RLH.Journey_Total_LeadTime
END DESC
END
It worked fine , but need some more modification.
I will consult later on those.
Thanks once more.
Subhro
June 1, 2010 at 2:27 am
You're welcome Subhro! 🙂
June 1, 2010 at 6:13 am
There is a problem with using CASE in an ORDER BY expression: SQL Server will not be able to make use of an index to order the rows (you'll always have at least one explicit Sort iterator in the query plan).
Generally speaking, it is better to use an alternative technique for these kinds of custom filtering/sorting requirements.
My favourite references on the subject are:
http://www.sommarskog.se/dyn-search.html
http://www.sommarskog.se/dynamic_sql.html
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 1, 2010 at 6:46 am
OK Paul I will see it.
Thanks
Subhro
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply