July 15, 2014 at 4:57 pm
I have a stored procedure that I would like to order by the order of the parameters it takes in starting for current item number 1, prior item number 1, current item number 2, prior item number 2, and so on.
currently I am ordering it by:
c.CurrentItemNumber
, p.PriorItemNumber
among other fields, but I would like to replace the part above with the parameters 1 through 5 (current and prior).
Is this possible?
This is my stored procedure for reference:
ALTER PROCEDURE [cost].[Ingredient_Cost_Comparison]
(
@CurrentSalesQuoteNumberNVARCHAR(20)
,@PriorSalesQuoteNumberNVARCHAR(20)
,@CurrentItemNumber1NVARCHAR(20)
,@PriorItemNumber1NVARCHAR(20)
,@CurrentItemNumber2NVARCHAR(20)
,@PriorItemNumber2NVARCHAR(20)
,@CurrentItemNumber3NVARCHAR(20)
,@PriorItemNumber3NVARCHAR(20)
,@CurrentItemNumber4NVARCHAR(20)
,@PriorItemNumber4NVARCHAR(20)
,@CurrentItemNumber5NVARCHAR(20)
,@PriorItemNumber5NVARCHAR(20)
)
AS
BEGIN
SELECT
s.CustomerNumberAS 'CurrentCustomerNumber_BillTo'
,s.SalesQuoteNumberAS 'CurrentSalesQuoteNumber'
,s.SalesQuoteLineNumberAS 'CurrentQuoteLineNumber'
,s.ItemNumberAS 'CurrentItemNumber'
,s.ItemDescriptionAS 'CurrentItemDescription'
,s.ComponentItemNumberAS 'CurrentComponentItemNumber'
,s.ComponentItemDescriptionAS 'CurrentComponentItemDescription'
,s.ComponentItemLevelAS 'CurrentComponentItemLevel'
,s.BOMQuantityAS 'CurrentBOMQuantity'
,s.BOMUnitOfMeasureAS 'CurrentBOMUnitOfMeasure'
,s.CostTypeUsedAS 'CurrentCostTypeUsed'
,s.CostMaterialWorksheetAS 'CurrentCostMaterialWorksheet'
,s.CostMaterialMarketExtendedAS 'CurrentCostMaterialMarketExtended'
,s.RequestedDeliveryDateAS 'CurrentRequestedDeliveryDate'
INTO #Current
FROM dw.cost.vStd_SalesQuoteCostLineBOM s (NOLOCK)
WHERE s.SalesQuoteNumber = @CurrentSalesQuoteNumber
AND s.ItemNumber IN (
ISNULL(@CurrentItemNumber1, '')
,ISNULL(@CurrentItemNumber2, '')
,ISNULL(@CurrentItemNumber3, '')
,ISNULL(@CurrentItemNumber4, '')
,ISNULL(@CurrentItemNumber5, '')
)
GROUP BY
s.CustomerNumber
,s.SalesQuoteNumber
,s.SalesQuoteLineNumber
,s.ItemNumber
,s.ItemDescription
,s.ComponentItemNumber
,s.ComponentItemDescription
,s.ComponentItemLevel
,s.BOMQuantity
,s.BOMUnitOfMeasure
,s.CostTypeUsed
,s.CostMaterialWorksheet
,s.CostMaterialMarketExtended
,s.RequestedDeliveryDate
ORDER BY
s.ItemNumber
,s.ComponentItemLevel
,s.ComponentItemNumber
SELECT
s.CustomerNumberAS 'PriorCustomerNumber_BillTo'
,s.SalesQuoteNumberAS 'PriorSalesQuoteNumber'
,s.SalesQuoteLineNumberAS 'PriorQuoteLineNumber'
,s.ItemNumberAS 'PriorItemNumber'
,s.ItemDescriptionAS 'PriorItemDescription'
,s.ComponentItemNumberAS 'PriorComponentItemNumber'
,s.ComponentItemDescriptionAS 'PriorComponentItemDescription'
,s.ComponentItemLevelAS 'PriorComponentItemLevel'
,s.BOMQuantityAS 'PriorBOMQuantity'
,s.BOMUnitOfMeasureAS 'PriorBOMUnitOfMeasure'
,s.CostTypeUsedAS 'PriorCostTypeUsed'
,s.CostMaterialWorksheetAS 'PriorCostMaterialWorksheet'
,s.CostMaterialMarketExtendedAS 'PriorCostMaterialMarketExtended'
,s.RequestedDeliveryDateAS 'PriorRequestedDeliveryDate'
INTO #Prior
FROM dw.cost.vStd_SalesQuoteCostLineBOM s (NOLOCK)
WHERE s.SalesQuoteNumber = @PriorSalesQuoteNumber
AND s.ItemNumber IN (
ISNULL(@PriorItemNumber1, '')
,ISNULL(@PriorItemNumber2, '')
,ISNULL(@PriorItemNumber3, '')
,ISNULL(@PriorItemNumber4, '')
,ISNULL(@PriorItemNumber5, '')
)
GROUP BY
s.CustomerNumber
,s.SalesQuoteNumber
,s.SalesQuoteLineNumber
,s.ItemNumber
,s.ItemDescription
,s.ComponentItemNumber
,s.ComponentItemDescription
,s.ComponentItemLevel
,s.BOMQuantity
,s.BOMUnitOfMeasure
,s.CostTypeUsed
,s.CostMaterialWorksheet
,s.CostMaterialMarketExtended
,s.RequestedDeliveryDate
ORDER BY
s.ItemNumber
,s.ComponentItemLevel
,s.ComponentItemNumber
-- Output
SELECT
c.CurrentCustomerNumber_BillTo
,c.CurrentSalesQuoteNumber
,c.CurrentItemNumber
,MAX(c.CurrentItemDescription)AS 'CurrentItemDescription'
,c.CurrentComponentItemNumber
,MAX(c.CurrentComponentItemDescription)AS 'CurrentComponentItemDescription'
,c.CurrentComponentItemLevel
,SUM(c.CurrentBOMQuantity)AS 'CurrentBOMQuantity'
,c.CurrentBOMUnitOfMeasure
,c.CurrentCostTypeUsed
,SUM(c.CurrentCostMaterialWorksheet)AS 'CurrentCostMaterialWorksheet'
,SUM(c.CurrentCostMaterialMarketExtended)AS 'CurrentCostMaterialMarketExtended'
,MAX(c.CurrentRequestedDeliveryDate)AS 'CurrentRequestedDeliveryDate'
,p.PriorSalesQuoteNumber
,p.PriorItemNumber
,MAX(p.PriorItemDescription)AS 'PriorItemDescription'
,p.PriorComponentItemNumber
,MAX(p.PriorComponentItemDescription)AS 'PriorComponentItemDescription'
,p.PriorComponentItemLevel
,sum(p.PriorBOMQuantity)AS 'PriorBOMQuantity'
,p.PriorBOMUnitOfMeasure
,p.PriorCostTypeUsed
,SUM(p.PriorCostMaterialWorksheet)AS 'PriorCostMaterialWorksheet'
,SUM(p.PriorCostMaterialMarketExtended)AS 'PriorCostMaterialMarketExtended'
,MAX(p.PriorRequestedDeliveryDate)AS 'PriorRequestedDeliveryDate'
,CASE
WHEN (LEN(SUM(c.CurrentCostMaterialMarketExtended)) > 0 OR LEN(SUM(p.PriorCostMaterialMarketExtended)) > 0)
THEN ISNULL(SUM(c.CurrentCostMaterialMarketExtended), 0) - ISNULL(SUM(p.PriorCostMaterialMarketExtended), 0)
ELSE
NULL
ENDAS 'FavUnfavCostChange'
,ISNULL(SUM(c.CurrentBOMQuantity), 0)
-
ISNULL(SUM(p.PriorBOMQuantity), 0)AS 'FormulaChange'
FROM #Current C
LEFT OUTER JOIN #Prior P
ON p.PriorCustomerNumber_BillTo = c.CurrentCustomerNumber_BillTo
AND p.PriorItemNumber = c.CurrentItemNumber
AND p.PriorComponentItemNumber = c.CurrentComponentItemNumber
AND p.PriorComponentItemLevel = c.CurrentComponentItemLevel
GROUP BY
c.CurrentCustomerNumber_BillTo
,c.CurrentSalesQuoteNumber
,p.PriorSalesQuoteNumber
,c.CurrentItemNumber
,p.PriorItemNumber
,c.CurrentComponentItemNumber
,p.PriorComponentItemNumber
,c.CurrentBOMUnitOfMeasure
,p.PriorBOMUnitOfMeasure
,c.CurrentCostTypeUsed
,p.PriorCostTypeUsed
,c.CurrentComponentItemLevel
,p.PriorComponentItemLevel
ORDER BY
c.CurrentItemNumber
,p.PriorItemNumber
,c.CurrentComponentItemLevel
,p.PriorComponentItemLevel
,c.CurrentComponentItemNumber
,p.PriorComponentItemNumber
,c.CurrentCostTypeUsed
DROP TABLE #Current, #Prior;
END
July 15, 2014 at 11:53 pm
No Order by can not be done using paramerter of SP. For this you need to select these parameters as column and then you can use for ordering purpose.
Could you please Proviod sample data? What ordering you want ?
Regards,
Sagar S
July 16, 2014 at 2:57 am
I'm not sure if I understand your question correct, but maybe this can help. You can use a CASE statement in an ORDER BY like this:
SELECT
...
ORDER BY CASE
WHEN ItemNumber = @CurrentItemNumber1
THEN 0
WHEN PriorItemNumber = @PriorItemNumber1
THEN 1
WHEN ItemNumber = @CurrentItemNumber2
THEN 2
WHEN PriorItemNumber = @PriorItemNumber2
THEN 3
WHEN ItemNumber = @CurrentItemNumber3
THEN 4
WHEN PriorItemNumber = @PriorItemNumber3
THEN 5
WHEN ItemNumber = @CurrentItemNumber4
THEN 6
WHEN PriorItemNumber = @PriorItemNumber4
THEN 7
WHEN ItemNumber = @CurrentItemNumber5
THEN 8
WHEN PriorItemNumber = @PriorItemNumber5
THEN 9
END
, {other columns}
The above code will order on the results of the CASE statement (e.g. number 0 to 9)
July 18, 2014 at 3:01 am
You could add the parameter to your select statement, for example:
SELECT [id],[description],(CASE WHEN @Parameter = ...etc ) AS [order]
FROM tablename
ORDER BY [order]
The logical instruction order of SQL (from -> where -> group by -> having -> select -> order by) allows you to use an alias in the order by. You can also write out the case statement as described in the solution above, check your query execution plan to determine which option provides the best performance for your environment.
August 14, 2014 at 12:24 am
I am getting an error 'Conversion failed when converting datetime from character string.' when I am executing this stored procedure as DATEOFCOMM is declared as datetime format. but I am not able to find the reason why conversion is getting failed in ORDER BY clause
IF(@vRPTFOR='RIAR' OR @vRPTFOR IS NULL)
BEGIN
SELECT * FROM #TEMPPOLICYLIFEREPORT ORDER BY
CASE WHEN @vGROUPBY='TREATYID' THEN TREATYID ELSE
CASE WHEN @vGROUPBY='BUSNSOURCEID' THEN BUSNSOURCEID ELSE
CASE WHEN @vGROUPBY='POLICYID' THEN POLICYID ELSE
CASE WHEN @vGROUPBY='REASONID' THEN REASONID ELSE
TREATYID END END END END,
CASE WHEN @vORDERBY='POLICYID' THEN POLICYID ELSE
CASE WHEN @vORDERBY='MEMBERID' THEN MEMBERID ELSE
CASE WHEN @vORDERBY='DATEOFCOMM' THEN DATEOFCOMM ELSE
POLICYID END END END
END
August 14, 2014 at 9:59 am
Hi.
I am not an expert, but do you need to use the single quotes around the column names in your case expression?
Good luck 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply