how to have a conditional join, or a conditional union based on parameters passed to stored procedure

Question

Post reply

how to have a conditional join, or a conditional union based on parameters passed to stored procedure

itortu

SSCommitted

Points: 1914
More actions
July 15, 2014 at 12:12 pm

#291152

I am writing a stored procedure that takes in a customer number, a current (most recent) sales order quote, a prior (to most current) sales order quote, a current item 1, and a prior item 1, all of these parameters are required.
Then I have current item 2, prior item 2, current item 3, prior item 3, which are optional.
I added an IF to check for the value of current item 2, prior item 2, current item 3, prior item 3, if there are values, then variable tables are created and filled with data, then are retrieved.
As it is, my stored procedure returns 3 sets of data when current item 1, prior item 1, current item 2, prior item 2, current item 3, prior item 3 are passed to it, and only one if 2, and 3 are omitted.
I would like to learn how can I return this as a one data set, either using a full outer join, or a union all?
I have been looking if writing a condition to use a union is possible but I think it is not. Or at least not in a way that I am able to understand.
I am hoping someone in this forum can offer some guidance on how to approach to this problem, I am sure this is not the first time someone has tried to do something similar. I hope I am fortunate enough to get some much needed help.
I am including a copy of my stored procedure as it is.
Please let me know what else I need to include in order to better receive help.
Thank you very much.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Prudhvi _SQL SSC Veteran Points: 209 More actions · Answer 1

Try this....

CREATE PROCEDURE [cost].[Ingredient_Cost_Comparison]

(

@CustomerNumber_BillToNVARCHAR(20)

,@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 DISTINCT

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.CustomerNumber IN @CustomerNumber_BillTo

ANDs.SalesQuoteNumber = @CurrentSalesQuoteNumber

AND s.ItemNumber IN (@CurrentItemNumber1,@CurrentItemNumber2,@CurrentItemNumber3)

SELECT DISTINCT

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 (@PriorItemNumber1,@PriorItemNumber2,@PriorItemNumber3)

SELECT DISTINCT C.*, P.*

FROM #Current C

LEFT JOIN #Prior P ON P.PriorCustomerNumber_BillTo = C.CurrentCustomerNumber_BillTo

AND P.PriorComponentItemNumber = C.CurrentComponentItemNumber

AND P.PriorComponentItemLevel = C.CurrentComponentItemLevel;

DROP TABLE #Current, #Prior;

END

Thanks,

Raj

itortu SSCommitted Points: 1914 More actions · Answer 2

I see what you are doing, I think I tried to do that but then I was not able to figure out how to group it by item 1, item 2, item 3, and so on

Thank you

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Why all the DISTINCTs Aspirant?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

itortu SSCommitted Points: 1914 More actions · Answer 4

I need to be able to group by each item

Something like:

current sales quote current item 1 current requesteddeliverydate

prior sales quote prior item 1 priorrequesteddeliverydate

current sales quote current item 2 current requesteddeliverydate

prior sales quote prior item 2 priorrequesteddeliverydate

current sales quote current item 3 current requesteddeliverydate

prior sales quote prior item 3 priorrequesteddeliverydate

and so on (up to 5)

itortu SSCommitted Points: 1914 More actions · Answer 5

itortu

SSCommitted

Points: 1914

July 15, 2014 at 2:17 pm

#1729633

I am adding some test data. I hope this helps.

x SSC-Insane Points: 23660 More actions · Answer 6

GilaMonster (7/15/2014)
Why all the DISTINCTs Aspirant?

Helps his nolock's run faster!