Capturing a result set from a SPROC call from within SQL

I have a stored procedure that calculates part pricing, list prices, price multiples etc.

Within that stored procedure I have several declares and set statements (populated by selects from parameter tables) that are used to determine how a part is priced.

Currently when the stored procedure is called I pass the part number and customer number which are critical to how the declared values are used.

This is a SPROC that I want to be able to incorporate in TSQL whenever I need to calculate the correct part pricing.

I'm trying to figure out how I can call the Pricing stored procedure and use the result set from the call.

I've contemplated turning the pricing procedure into a table-valued function but It would require declares and sets which I believe cannot be done in a table-valued function.

Any ideas?

Thanks
jdr0606Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
This is a SPROC that I want to be able to incorporate in TSQL whenever I need to calculate the correct part pricing.
What does this mean??

Currently when the stored procedure is called I pass the part number and customer number which are critical to how the declared values are used.
You're already calling it..
0
Mark WillsTopic AdvisorCommented:
You can use DECLARE and SET in a user defined function.

Cannot use EXEC, table definitions and a few other things in a Function.

Probably best to show us the Stored Procedure and see if we can help convert it, or, determine if it is convertible, or, maybe suggest a price matrix instead of a stored procedure .
0
ZberteocCommented:
If you have a stored procedure called spProcedure that returns a result set like

col1, col2, col3

you can grab that result set in any existing temporary or permanent table providing the fact that it had been already created. Something like this:

CREATE TABLE #results(col1 type1, col2 type2, col3 type3)
INSERT INTO #results(col1, col2, col3 ) EXEC spProcedure
SELECT * FROM #results
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

jdr0606Author Commented:
Sorry if I was not clear with my question.

I have attached the Pricing Stored Procedure that I want to be able to call and capture the results of the select in the SPROC.

Ideally I'd like to turn it into a Tabular function that can be called as a standard pricing call.
PricingSPROC.txt
0
Mark WillsTopic AdvisorCommented:
Looks like it can be readily turned into a function...

Good example with declaratives can be found : https://technet.microsoft.com/en-us/library/ms191165(v=sql.105).aspx

Basically it would be something like
CREATE FUNCTION [dbo].[udfGetCUSTtPricing]  ( @itemnmbr varchar(31),  @custnmbr varchar(15))
RETURNS @CustomerPrice TABLE 
(
    -- Columns returned by the function
    Custnbr varchar(15) PRIMARY KEY NOT NULL, 
    itemnbr varchar(31) NULL, 
    listprice money,
	TrueSEPrice money,
	SEPrice money
--    Price columns and whatever else go here
)
AS 
BEGIN

declare @RSKDiff	    numeric(19,5)
declare @DefaultDiff    numeric(19,5)
declare @OBOXDiff	    numeric(19,5)
declare @Override	    numeric(19,5)
declare @NetPrice	    numeric(19,5)
declare @NonStockDiff   numeric(19,5)
declare @RSKCode	    varchar(15)

set @RSKDiff=(select value from dynamics_ext.dbo.udtCUSTDefault where Description='RSK')
set @DefaultDiff=(select value from dynamics_ext.dbo.udtCUSTDefault where Description='DEFAULT')
set @OBOXDiff=(select value from dynamics_ext.dbo.udtCUSTDefault where Description='OBOX')
set @Override=(select value from dynamics_ext.dbo.udtCUSTDefault where Description='OVERRIDE')
set @NetPrice=(select value from dynamics_ext.dbo.udtCUSTDefault where Description='NETPRICE')
set @NonStockDiff=(select value from dynamics_ext.dbo.udtCUSTDefault where Description='NONSTOCKDIFF')
set @RSKCode=(select code from dynamics_ext.dbo.udtCUSTDefault where Description='RSKCode')



Insert @CustomerPrice 
select  @custnbr, @itemnbr,

    'ListPrice'=	--This is the ListPrice for all Parts regardless of Customer, does not include overrides
		  case when iv1.itemtype = 7 then 0
		  when iv1.itemnmbr = 'LABEL' then eiv6.listpricebasis_1
		  when iv1.itemnmbr = 'PANEL LABEL' then eiv6.listpricebasis_1
		  when iv1.itemtype = '5' then eiv6.listpricebasis_1
		  when iv1.pricegroup is NULL then eiv6.listpricebasis_1
		  /* This is where the special calculation begins */
		  when iv1.pricegroup='SALE' and partner.itemnmbr is not null and partner.ProgramType='SB' then eiv6.listpricebasis_3  -- SBUY
		  when iv1.pricegroup='SALE' and partner.itemnmbr is not null and partner.ProgramType<>'SB' then eiv6.listpricebasis_2  -- Sale Not SBUY
		  when iv1.pricegroup='CUST' and partner.itemnmbr is not null  then 
			 (case when eiv6.listpricebasis_2<>0 then eiv6.listpricebasis_2 else eiv6.listpricebasis_1 end)  -- CUST Special List
		  when iv1.pricegroup<>'CUST' and partner.itemnmbr is not null  then 
			 (case when eiv6.ListPricebasis_2=0 then eiv6.listpricebasis_1
				else eiv6.listpricebasis_2 end)
		  when iv1.itemnmbr like 'OBOX%' then eiv6.listpricebasis_1 -- OBOX
		  else eiv6.listpricebasis_1 end
  
    , 'TrueSEPrice'=  --This is the List Price for the customer, may also include overrides and Non-Stocks
		  (case when iv1.itemtype = 7 then 0
		  when iv1.itemnmbr = 'LABEL' then eiv6.listpricebasis_1
		  when iv1.itemnmbr = 'PANEL LABEL' then eiv6.listpricebasis_1
		  when iv1.itemtype = '5' then eiv6.listpricebasis_1
		  when iv1.pricegroup is NULL then eiv6.listpricebasis_1
		  /* This is where the special calculation begins */
		  when eiv8.multiplierval > 0 and eiv8.expndate > CONVERT(varchar, GETDATE(), 120) then eiv8.multiplierval -- Overrides
		  when iv1.pricegroup='SALE' and partner.itemnmbr is not null and partner.ProgramType='SB' then eiv6.listpricebasis_3*@NetPrice  -- SBUY
		  when iv1.pricegroup='SALE' and partner.itemnmbr is not null and partner.ProgramType<>'SB' then eiv6.listpricebasis_2*partner.Differential  -- Sale Not SBUY
		  when iv1.pricegroup='CUST' and partner.itemnmbr is not null  then eiv6.listpricebasis_2 *partner.Differential  -- CUST Special List
		  when iv1.pricegroup<>'CUST' and partner.itemnmbr is not null  then 
		  (case when eiv7.basistouse_1=1 then eiv6.listpricebasis_1
				  when eiv7.basistouse_1=2 then eiv6.listpricebasis_2
				  when eiv7.basistouse_1=3 then eiv6.listpricebasis_3
				  when eiv7.basistouse_1=4 then eiv6.listpricebasis_4
				  when eiv7.basistouse_1=5 then eiv6.listpricebasis_5 end)* partner.Differential  -- Not CUST Special List
		  when iv1.itmclscd ='NON-IV' and ivlog.vendorcode=@RSKCode then (ivlog.unitprice/eiv7.multiplier_1)*@RSKDiff   --Non Stock - RSK
		  when iv1.itmclscd ='NON-IV' and ivlog.vendorcode<>@RSKCode then (ivlog.unitprice/eiv7.multiplier_1)*@NonStockDiff   --NonStock - Not RSK
		  when iv1.itemnmbr like 'OBOX%' then eiv6.listpricebasis_1 * @OBOXDiff -- OBOX
		  when eiv7.basistouse_1 = 1 then eiv6.listpricebasis_1 * @DefaultDiff
		  when eiv7.basistouse_1 = 2 then eiv6.listpricebasis_2 * @DefaultDiff
		  when eiv7.basistouse_1 = 3 then eiv6.listpricebasis_3 * @DefaultDiff
		  when eiv7.basistouse_1 = 4 then eiv6.listpricebasis_4 * @DefaultDiff
		  when eiv7.basistouse_1 = 5 then eiv6.listpricebasis_5 * @DefaultDiff
		  when eiv7.basistouse_1 = 6 and rm1.prclevel ='KELE SING' then eiv6.cost * multiplier_1
		  when eiv7.basistouse_1 = 6 and rm1.prclevel ='KELE' then eiv6.cost * multiplier_1
		  else eiv6.listpricebasis_1 * @DefaultDiff end)

    , 'SEPrice'=	-- This is the price that CUST pays Kele
		  (case when iv1.itemtype = 7 then 0
		  when iv1.itemnmbr = 'LABEL' then eiv6.listpricebasis_1
		  when iv1.itemnmbr = 'PANEL LABEL' then eiv6.listpricebasis_1
		  when iv1.itemtype = '5' then eiv6.listpricebasis_1
		  when iv1.pricegroup is NULL then eiv6.listpricebasis_1
		  /* This is where the special calculation begins */
		  when iv1.pricegroup='SALE' and partner.itemnmbr is not null and partner.ProgramType='SB' then eiv6.listpricebasis_3*@NetPrice  -- SBUY
		  when iv1.pricegroup='SALE' and partner.itemnmbr is not null and partner.ProgramType<>'SB' then eiv6.listpricebasis_2*partner.Differential  -- Sale Not SBUY
		  when iv1.pricegroup='CUST' and partner.itemnmbr is not null  then eiv6.listpricebasis_2 *partner.Differential  -- CUST Special List
		  when iv1.pricegroup<>'CUST' and partner.itemnmbr is not null  then 
		  			 (case when eiv6.ListPricebasis_2=0 then eiv6.listpricebasis_1
				else eiv6.listpricebasis_2 end) *partner.Differential
		  when iv1.itmclscd ='NON-IV' and ivlog.vendorcode=@RSKCode then (ivlog.unitprice/eiv7.multiplier_1)*@RSKDiff   --Non Stock - RSK
		  when iv1.itmclscd ='NON-IV' and ivlog.vendorcode<>@RSKCode then (ivlog.unitprice/eiv7.multiplier_1)*@NonStockDiff   --NonStock - Not RSK
		  when iv1.itemnmbr like 'OBOX%' then eiv6.listpricebasis_1 * @OBOXDiff -- OBOX
		  when eiv7.basistouse_1 = 1 then eiv6.listpricebasis_1*@DefaultDiff
		  when eiv7.basistouse_1 = 2 then eiv6.listpricebasis_2*@DefaultDiff
		  when eiv7.basistouse_1 = 3 then eiv6.listpricebasis_3*@DefaultDiff
		  when eiv7.basistouse_1 = 4 then eiv6.listpricebasis_4*@DefaultDiff
		  when eiv7.basistouse_1 = 5 then eiv6.listpricebasis_5*@DefaultDiff
		  when eiv7.basistouse_1 = 6 and rm1.prclevel ='KELE SING' then eiv6.cost
		  when eiv7.basistouse_1 = 6 and rm1.prclevel ='KELE' then eiv6.cost
		  else eiv6.listpricebasis_1*@DefaultDiff end)

    , 'CustomerPrice'=  --This is the List Price for the customer, may also include overrides and Non-Stocks
		  (case when iv1.itemtype = 7 then 0
		  when iv1.itemnmbr = 'LABEL' then eiv6.listpricebasis_1
		  when iv1.itemnmbr = 'PANEL LABEL' then eiv6.listpricebasis_1
		  when iv1.itemtype = '5' then eiv6.listpricebasis_1
		  when iv1.pricegroup is NULL then eiv6.listpricebasis_1
		  /* This is where the special calculation begins */
		  when eiv8.multiplierval > 0 and eiv8.expndate > CONVERT(varchar, GETDATE(), 120) then eiv8.multiplierval -- Overrides
		  when iv1.pricegroup='SALE' and partner.itemnmbr is not null and partner.ProgramType='SB' then eiv6.listpricebasis_3  -- SBUY
		  when iv1.pricegroup='SALE' and partner.itemnmbr is not null and partner.ProgramType<>'SB' then 
				eiv6.listpricebasis_2*cast(right(rtrim(rm1.prclevel),3) as numeric(9,3))/1000  -- Sale Not SBUY - CUST Only
		  when iv1.pricegroup='CUST' and partner.itemnmbr is not null  then eiv6.listpricebasis_2*eiv7.multiplier_1-- CUST Special List
		  when iv1.pricegroup<>'CUST' and partner.itemnmbr is not null  then 
		  (case when eiv7.basistouse_1=1 then eiv6.listpricebasis_1
				  when eiv7.basistouse_1=2 then eiv6.listpricebasis_2
				  when eiv7.basistouse_1=3 then eiv6.listpricebasis_3
				  when eiv7.basistouse_1=4 then eiv6.listpricebasis_4
				  when eiv7.basistouse_1=5 then eiv6.listpricebasis_5 end)*eiv7.multiplier_1  -- Not CUST Special List
		  when iv1.itmclscd ='NON-IV' and ivlog.vendorcode=@RSKCode then ivlog.unitprice   --Non Stock - RSK
		  when iv1.itmclscd ='NON-IV' and ivlog.vendorcode<>@RSKCode then ivlog.unitprice   --NonStock - Not RSK
		  when iv1.itemnmbr like 'OBOX%' then eiv6.listpricebasis_1 *eiv7.multiplier_1 -- OBOX
		  when eiv7.basistouse_1 = 1 then eiv6.listpricebasis_1 *eiv7.multiplier_1
		  when eiv7.basistouse_1 = 2 then eiv6.listpricebasis_2 *eiv7.multiplier_1
		  when eiv7.basistouse_1 = 3 then eiv6.listpricebasis_3 *eiv7.multiplier_1
		  when eiv7.basistouse_1 = 4 then eiv6.listpricebasis_4 *eiv7.multiplier_1
		  when eiv7.basistouse_1 = 5 then eiv6.listpricebasis_5 *eiv7.multiplier_1
		  when eiv7.basistouse_1 = 6 and rm1.prclevel ='KELE SING' then eiv6.cost * multiplier_1
		  when eiv7.basistouse_1 = 6 and rm1.prclevel ='KELE' then eiv6.cost * multiplier_1
		  else eiv6.listpricebasis_1 *eiv7.multiplier_1 end)

    , 'OveridePrice'=(case when eiv8.multiplierval > 0 and eiv8.expndate > CONVERT(varchar, GETDATE(), 120) then eiv8.multiplierval*@Override -- Overrides 
		  	    else 0 end)
	   ,rtrim(iv1.itemnmbr) itemnmbr
	   , iv1.tcc ItemMasterTCC
	   ,eiv7.multiplier_1 Eiv7Multiplier
	   ,eiv6.listpricebasis_1 EIV6LP1
	   ,eiv6.listpricebasis_2 EIV6LP2
	   ,eiv6.listpricebasis_3 EIV6LP3
	   ,eiv6.listpricebasis_4 EIV6LP4
	   ,eiv6.listpricebasis_5 EIV6LP5
	   ,eiv6.cost EIV6Cost
	   ,eiv7.basistouse_1 EIV7BasisToUse
	   ,rm1.custnmbr CustMasterNumber
	   , rm1.prclevel CustMasterMultiplier
	   , iv1.itemtype ItemMasterItemType
	   ,eiv8.custnmbr OverCustNumber
	   ,eiv8.expndate OverExpDate
	   ,eiv8.multiplierval OverAmount
	   , partner.Differential PartnerDifferential
	   , iv1.pricegroup ItemPriceGroup
	   , partner.tcc PartnerTCC
	   , iv1.itemdesc ItemMasterDesc
	   , @RSKDiff RSKDefault
	   , @DefaultDiff DefaultDiff
	   , @OBOXDiff OBOXDiff
	   , @Override OverrideDef
	   , @NetPrice NetPriceDiff
	   , iv1.currcost ItemMasterCurrCost
	   , ivlog.unitprice NonStockUnit
from tempload.dbo.iv00101_Testing iv1
left join tempload.dbo.eiv00600_Testing eiv6 on eiv6.itemnmbr=iv1.itemnmbr
left join dynamics_ext.dbo.udtPartnerPricing_testing partner on partner.tcc=iv1.tcc
left join tempload.dbo.eiv00800_Testing eiv8 on eiv8.itemnmbr=iv1.itemnmbr
    and eiv8.expndate>getdate()
    and eiv8.custnmbr=@custnmbr
left join keleprd.mis.dbo.udtSE_Xref xref on xref.itemnmbr=iv1.itemnmbr
left join keleg.dbo.rm00101 rm1 on rm1.custnmbr=@custnmbr
left join tempload.dbo.eiv00700_Testing eiv7 on eiv7.pricegroup=iv1.pricegroup
    and eiv7.prclevel=rm1.prclevel
left join dynamics_ext.dbo.udtNonInventoryLog ivlog on ivlog.NonInventoryID=iv1.itemnmbr
    and ivlog.vendorcode=@RSKCode
where 
     iv1.itemtype in ('1','3')
    and iv1.tcc not like '%promo%'
    and iv1.tcc not like '*%'
    and iv1.itemnmbr not like '%promo%'
    and iv1.itemdesc not like '%promo%'
    and iv1.itemcode<>'INTERNAL_ONLY'
    and iv1.itemcode <>'GP_ONLY'
    and iv1.tcc =@itemnmbr
END
RETURN
GO

Open in new window

and use it like any other table  (can be joined, cross apply etc) except it needs the two params supplied
select *
from dbo.udfGetCUSTtPricing  ( 'Item007', 'Cust007')
0
jdr0606Author Commented:
Mark, i think it's close but I'm getting a
Msg 156, Level 15, State 1, Procedure udfGetCUSTtPricing, Line 188
Incorrect syntax near the keyword 'RETURN'.
when I try and create the function, do you see where that error is?

Thanks
PricingSPROC_Updated.txt
0
Mark WillsTopic AdvisorCommented:
Would be inclined to set up each of your price components as declaratives, populate those individually then insert them into the table. Just a thought....

To use in say an order pricing scenario you would do something like

select o,cust,o.item,p.prices
from orders O
cross apply  dbo.udfGetCUSTtPricing  ( o.item, o.cust) p

does that make sense ?
0
Mark WillsTopic AdvisorCommented:
Didnt see your post until after I posted the above....

It will probably be a column mismatch - you need to finish off the table definition...

or use column names and comment out the rest just to get started
0
jdr0606Author Commented:
Mark, I have set the core 7 columns I want to capture and comment out the remainder but still get the
Msg 156, Level 15, State 1, Procedure udfGetCUSTtPricing, Line 191
Incorrect syntax near the keyword 'RETURN'.

Can you see anything I'm still missing?

Thanks!
PricingSPROC_AnotherUpdat.txt
0
Mark WillsTopic AdvisorCommented:
a couple of conflicts with names, so went with the declaratives approach and do the insert down near the end

also put the return inside the begin/end

CREATE FUNCTION [dbo].[udfGetCUSTtPricing]  ( @itemnmbr varchar(31),  @custnmbr varchar(15))
RETURNS @CustomerPriceTable TABLE 
(
    -- Columns returned by the function
    Custnmbr varchar(15) PRIMARY KEY NOT NULL, 
    itemnmbr varchar(31) NULL, 
    listprice money,
	TrueSEPrice money,
	SEPrice money,
	CustomerPrice money,
	OveridePrice money

--    Price columns and whatever else go here
)
AS 
BEGIN

declare @RSKDiff	    numeric(19,5)
declare @DefaultDiff    numeric(19,5)
declare @OBOXDiff	    numeric(19,5)
declare @Override	    numeric(19,5)
declare @NetPrice	    numeric(19,5)
declare @NonStockDiff   numeric(19,5)
declare @RSKCode	    varchar(15)

set @RSKDiff=(select value from dynamics_ext.dbo.udtCUSTDefault where Description='RSK')
set @DefaultDiff=(select value from dynamics_ext.dbo.udtCUSTDefault where Description='DEFAULT')
set @OBOXDiff=(select value from dynamics_ext.dbo.udtCUSTDefault where Description='OBOX')
set @Override=(select value from dynamics_ext.dbo.udtCUSTDefault where Description='OVERRIDE')
set @NetPrice=(select value from dynamics_ext.dbo.udtCUSTDefault where Description='NETPRICE')
set @NonStockDiff=(select value from dynamics_ext.dbo.udtCUSTDefault where Description='NONSTOCKDIFF')
set @RSKCode=(select code from dynamics_ext.dbo.udtCUSTDefault where Description='RSKCode')

declare @listprice money,
	@TrueSEPrice money,
	@SEPrice money,
	@CustomerPrice money,
	@OveridePrice money


   Select
    @ListPrice = 	--This is the ListPrice for all Parts regardless of Customer, does not include overrides
		  case when iv1.itemtype = 7 then 0
		  when iv1.itemnmbr = 'LABEL' then eiv6.listpricebasis_1
		  when iv1.itemnmbr = 'PANEL LABEL' then eiv6.listpricebasis_1
		  when iv1.itemtype = '5' then eiv6.listpricebasis_1
		  when iv1.pricegroup is NULL then eiv6.listpricebasis_1
		  /* This is where the special calculation begins */
		  when iv1.pricegroup='SALE' and partner.itemnmbr is not null and partner.ProgramType='SB' then eiv6.listpricebasis_3  -- SBUY
		  when iv1.pricegroup='SALE' and partner.itemnmbr is not null and partner.ProgramType<>'SB' then eiv6.listpricebasis_2  -- Sale Not SBUY
		  when iv1.pricegroup='CUST' and partner.itemnmbr is not null  then 
			 (case when eiv6.listpricebasis_2<>0 then eiv6.listpricebasis_2 else eiv6.listpricebasis_1 end)  -- CUST Special List
		  when iv1.pricegroup<>'CUST' and partner.itemnmbr is not null  then 
			 (case when eiv6.ListPricebasis_2=0 then eiv6.listpricebasis_1
				else eiv6.listpricebasis_2 end)
		  when iv1.itemnmbr like 'OBOX%' then eiv6.listpricebasis_1 -- OBOX
		  else eiv6.listpricebasis_1 end
 
    , @TrueSEPrice =  --This is the List Price for the customer, may also include overrides and Non-Stocks
		  (case when iv1.itemtype = 7 then 0
		  when iv1.itemnmbr = 'LABEL' then eiv6.listpricebasis_1
		  when iv1.itemnmbr = 'PANEL LABEL' then eiv6.listpricebasis_1
		  when iv1.itemtype = '5' then eiv6.listpricebasis_1
		  when iv1.pricegroup is NULL then eiv6.listpricebasis_1
		  /* This is where the special calculation begins */
		  when eiv8.multiplierval > 0 and eiv8.expndate > CONVERT(varchar, GETDATE(), 120) then eiv8.multiplierval -- Overrides
		  when iv1.pricegroup='SALE' and partner.itemnmbr is not null and partner.ProgramType='SB' then eiv6.listpricebasis_3*@NetPrice  -- SBUY
		  when iv1.pricegroup='SALE' and partner.itemnmbr is not null and partner.ProgramType<>'SB' then eiv6.listpricebasis_2*partner.Differential  -- Sale Not SBUY
		  when iv1.pricegroup='CUST' and partner.itemnmbr is not null  then eiv6.listpricebasis_2 *partner.Differential  -- CUST Special List
		  when iv1.pricegroup<>'CUST' and partner.itemnmbr is not null  then 
		  (case when eiv7.basistouse_1=1 then eiv6.listpricebasis_1
				  when eiv7.basistouse_1=2 then eiv6.listpricebasis_2
				  when eiv7.basistouse_1=3 then eiv6.listpricebasis_3
				  when eiv7.basistouse_1=4 then eiv6.listpricebasis_4
				  when eiv7.basistouse_1=5 then eiv6.listpricebasis_5 end)* partner.Differential  -- Not CUST Special List
		  when iv1.itmclscd ='NON-IV' and ivlog.vendorcode=@RSKCode then (ivlog.unitprice/eiv7.multiplier_1)*@RSKDiff   --Non Stock - RSK
		  when iv1.itmclscd ='NON-IV' and ivlog.vendorcode<>@RSKCode then (ivlog.unitprice/eiv7.multiplier_1)*@NonStockDiff   --NonStock - Not RSK
		  when iv1.itemnmbr like 'OBOX%' then eiv6.listpricebasis_1 * @OBOXDiff -- OBOX
		  when eiv7.basistouse_1 = 1 then eiv6.listpricebasis_1 * @DefaultDiff
		  when eiv7.basistouse_1 = 2 then eiv6.listpricebasis_2 * @DefaultDiff
		  when eiv7.basistouse_1 = 3 then eiv6.listpricebasis_3 * @DefaultDiff
		  when eiv7.basistouse_1 = 4 then eiv6.listpricebasis_4 * @DefaultDiff
		  when eiv7.basistouse_1 = 5 then eiv6.listpricebasis_5 * @DefaultDiff
		  when eiv7.basistouse_1 = 6 and rm1.prclevel ='KELE SING' then eiv6.cost * multiplier_1
		  when eiv7.basistouse_1 = 6 and rm1.prclevel ='KELE' then eiv6.cost * multiplier_1
		  else eiv6.listpricebasis_1 * @DefaultDiff end)

    , @SEPrice=	-- This is the price that CUST pays Kele
		  (case when iv1.itemtype = 7 then 0
		  when iv1.itemnmbr = 'LABEL' then eiv6.listpricebasis_1
		  when iv1.itemnmbr = 'PANEL LABEL' then eiv6.listpricebasis_1
		  when iv1.itemtype = '5' then eiv6.listpricebasis_1
		  when iv1.pricegroup is NULL then eiv6.listpricebasis_1
		  /* This is where the special calculation begins */
		  when iv1.pricegroup='SALE' and partner.itemnmbr is not null and partner.ProgramType='SB' then eiv6.listpricebasis_3*@NetPrice  -- SBUY
		  when iv1.pricegroup='SALE' and partner.itemnmbr is not null and partner.ProgramType<>'SB' then eiv6.listpricebasis_2*partner.Differential  -- Sale Not SBUY
		  when iv1.pricegroup='CUST' and partner.itemnmbr is not null  then eiv6.listpricebasis_2 *partner.Differential  -- CUST Special List
		  when iv1.pricegroup<>'CUST' and partner.itemnmbr is not null  then 
		  			 (case when eiv6.ListPricebasis_2=0 then eiv6.listpricebasis_1
				else eiv6.listpricebasis_2 end) *partner.Differential
		  when iv1.itmclscd ='NON-IV' and ivlog.vendorcode=@RSKCode then (ivlog.unitprice/eiv7.multiplier_1)*@RSKDiff   --Non Stock - RSK
		  when iv1.itmclscd ='NON-IV' and ivlog.vendorcode<>@RSKCode then (ivlog.unitprice/eiv7.multiplier_1)*@NonStockDiff   --NonStock - Not RSK
		  when iv1.itemnmbr like 'OBOX%' then eiv6.listpricebasis_1 * @OBOXDiff -- OBOX
		  when eiv7.basistouse_1 = 1 then eiv6.listpricebasis_1*@DefaultDiff
		  when eiv7.basistouse_1 = 2 then eiv6.listpricebasis_2*@DefaultDiff
		  when eiv7.basistouse_1 = 3 then eiv6.listpricebasis_3*@DefaultDiff
		  when eiv7.basistouse_1 = 4 then eiv6.listpricebasis_4*@DefaultDiff
		  when eiv7.basistouse_1 = 5 then eiv6.listpricebasis_5*@DefaultDiff
		  when eiv7.basistouse_1 = 6 and rm1.prclevel ='KELE SING' then eiv6.cost
		  when eiv7.basistouse_1 = 6 and rm1.prclevel ='KELE' then eiv6.cost
		  else eiv6.listpricebasis_1*@DefaultDiff end)

    , @CustomerPrice=  --This is the List Price for the customer, may also include overrides and Non-Stocks
		  (case when iv1.itemtype = 7 then 0
		  when iv1.itemnmbr = 'LABEL' then eiv6.listpricebasis_1
		  when iv1.itemnmbr = 'PANEL LABEL' then eiv6.listpricebasis_1
		  when iv1.itemtype = '5' then eiv6.listpricebasis_1
		  when iv1.pricegroup is NULL then eiv6.listpricebasis_1
		  /* This is where the special calculation begins */
		  when eiv8.multiplierval > 0 and eiv8.expndate > CONVERT(varchar, GETDATE(), 120) then eiv8.multiplierval -- Overrides
		  when iv1.pricegroup='SALE' and partner.itemnmbr is not null and partner.ProgramType='SB' then eiv6.listpricebasis_3  -- SBUY
		  when iv1.pricegroup='SALE' and partner.itemnmbr is not null and partner.ProgramType<>'SB' then 
				eiv6.listpricebasis_2*cast(right(rtrim(rm1.prclevel),3) as numeric(9,3))/1000  -- Sale Not SBUY - CUST Only
		  when iv1.pricegroup='CUST' and partner.itemnmbr is not null  then eiv6.listpricebasis_2*eiv7.multiplier_1-- CUST Special List
		  when iv1.pricegroup<>'CUST' and partner.itemnmbr is not null  then 
		  (case when eiv7.basistouse_1=1 then eiv6.listpricebasis_1
				  when eiv7.basistouse_1=2 then eiv6.listpricebasis_2
				  when eiv7.basistouse_1=3 then eiv6.listpricebasis_3
				  when eiv7.basistouse_1=4 then eiv6.listpricebasis_4
				  when eiv7.basistouse_1=5 then eiv6.listpricebasis_5 end)*eiv7.multiplier_1  -- Not CUST Special List
		  when iv1.itmclscd ='NON-IV' and ivlog.vendorcode=@RSKCode then ivlog.unitprice   --Non Stock - RSK
		  when iv1.itmclscd ='NON-IV' and ivlog.vendorcode<>@RSKCode then ivlog.unitprice   --NonStock - Not RSK
		  when iv1.itemnmbr like 'OBOX%' then eiv6.listpricebasis_1 *eiv7.multiplier_1 -- OBOX
		  when eiv7.basistouse_1 = 1 then eiv6.listpricebasis_1 *eiv7.multiplier_1
		  when eiv7.basistouse_1 = 2 then eiv6.listpricebasis_2 *eiv7.multiplier_1
		  when eiv7.basistouse_1 = 3 then eiv6.listpricebasis_3 *eiv7.multiplier_1
		  when eiv7.basistouse_1 = 4 then eiv6.listpricebasis_4 *eiv7.multiplier_1
		  when eiv7.basistouse_1 = 5 then eiv6.listpricebasis_5 *eiv7.multiplier_1
		  when eiv7.basistouse_1 = 6 and rm1.prclevel ='KELE SING' then eiv6.cost * multiplier_1
		  when eiv7.basistouse_1 = 6 and rm1.prclevel ='KELE' then eiv6.cost * multiplier_1
		  else eiv6.listpricebasis_1 *eiv7.multiplier_1 end)

    , @OveridePrice=  -- overrides
	      (case when eiv8.multiplierval > 0 and eiv8.expndate > CONVERT(varchar, GETDATE(), 120) then eiv8.multiplierval*@Override 
		  	    else 0 end)
	   --,rtrim(iv1.itemnmbr) itemnmbr
	   --, iv1.tcc ItemMasterTCC
	   --,eiv7.multiplier_1 Eiv7Multiplier
	   --,eiv6.listpricebasis_1 EIV6LP1
	   --,eiv6.listpricebasis_2 EIV6LP2
	   --,eiv6.listpricebasis_3 EIV6LP3
	   --,eiv6.listpricebasis_4 EIV6LP4
	   --,eiv6.listpricebasis_5 EIV6LP5
	   --,eiv6.cost EIV6Cost
	   --,eiv7.basistouse_1 EIV7BasisToUse
	   --,rm1.custnmbr CustMasterNumber
	   --, rm1.prclevel CustMasterMultiplier
	   --, iv1.itemtype ItemMasterItemType
	   --,eiv8.custnmbr OverCustNumber
	   --,eiv8.expndate OverExpDate
	   --,eiv8.multiplierval OverAmount
	   --, partner.Differential PartnerDifferential
	   --, iv1.pricegroup ItemPriceGroup
	   --, partner.tcc PartnerTCC
	   --, iv1.itemdesc ItemMasterDesc
	   --, @RSKDiff RSKDefault
	   --, @DefaultDiff DefaultDiff
	   --, @OBOXDiff OBOXDiff
	   --, @Override OverrideDef
	   --, @NetPrice NetPriceDiff
	   --, iv1.currcost ItemMasterCurrCost
	   --, ivlog.unitprice NonStockUnit

   from tempload.dbo.iv00101_Testing iv1
   left join tempload.dbo.eiv00600_Testing eiv6 on eiv6.itemnmbr=iv1.itemnmbr
   left join dynamics_ext.dbo.udtPartnerPricing_testing partner on partner.tcc=iv1.tcc
   left join tempload.dbo.eiv00800_Testing eiv8 on eiv8.itemnmbr=iv1.itemnmbr and eiv8.expndate>getdate() and eiv8.custnmbr=@custnmbr
   left join keleprd.mis.dbo.udtSE_Xref xref on xref.itemnmbr=iv1.itemnmbr
   left join keleg.dbo.rm00101 rm1 on rm1.custnmbr=@custnmbr
   left join tempload.dbo.eiv00700_Testing eiv7 on eiv7.pricegroup=iv1.pricegroup and eiv7.prclevel=rm1.prclevel
   left join dynamics_ext.dbo.udtNonInventoryLog ivlog on ivlog.NonInventoryID=iv1.itemnmbr and ivlog.vendorcode=@RSKCode

   where iv1.itemtype in ('1','3')
   and iv1.tcc not like '%promo%'
   and iv1.tcc not like '*%'
   and iv1.itemnmbr not like '%promo%'
   and iv1.itemdesc not like '%promo%'
   and iv1.itemcode<>'INTERNAL_ONLY'
   and iv1.itemcode <>'GP_ONLY'
   and iv1.tcc =@itemnmbr


   Insert @CustomerPriceTable 
   select  @custnmbr, @itemnmbr,@listprice,@TrueSEPrice,@SEPrice,@CustomerPrice,@OveridePrice

   RETURN

END;
GO

Open in new window

2

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jdr0606Author Commented:
Mark, thinks for the assistance, very appreciated!
0
jdr0606Author Commented:
Thanks Again!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Stored Proc

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.