I wrote a SP to update a table that is 98 column across. This is a dynamic SP.

I have a few questions:
1. Is there a better way to deal with table locking
2. Is there anyway to get around it
3. Can anyone make suggestions to the code below (is it optimized)

We have added index and keys to all the table involved, tried different
renditions of the code below. This handles batch processing and ad-hoc.
Basically I am at my wits end and in alot of trouble if this doesn't work. I
will admit that I completely forgot about table locking when I designed this
process...... Any and all ideas are appreciated

CREATE PROCEDURE dbo.usp_Upd_Cr_Lg_Sku_Channel_Dollarization @dept as
varchar(10)

AS
SET NoCount On

--Declare variables and counter
Declare @dept1 as varchar(10), @Item_id1 as varchar (50), @cnt varchar(2)

--intializing the variables
If @dept = ''
BEGIN
Select @dept1 = null, @dept = 'null'
END
Else
Begin
Select @dept = '''' + rtrim(@dept) + '''', @dept1 = rtrim(@dept)
End

--Deletes the table when dept and item_id are null
--Should be used during batch processing
Delete from dbo.Cr_Lg_Sku_Channel_Dollarization
Where (@dept1 is null)
--and @Item_id1 is null

--Inserts all records into the table when dept and item_id are null
--Should be used during batch processing

Insert Into Cr_Lg_Sku_Channel_Dollarization
Select Distinct Pyramid, 'Y' as Logility_Flag, Dept,
Item_Id, Item_Desc, Channel_Code,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
getdate()
From cr_LG_RSLT_FCST, cr_CHANNEL
Where (@dept1 is null)
Order by Dept, Item_Id, Channel_Code

set @cnt = '1'

WHILE (@cnt <> 19)
BEGIN

--This will only initialize the dept or sku to zero
EXEC('Update Cr_Lg_Sku_Channel_Dollarization
Set Splt_QTY_' + @cnt + ' = 0,
Extd_Retail_' + @cnt + ' = 0,
Extd_Cost_' + @cnt + ' = 0,
Extd_Yen_Retail_' + @cnt + ' = 0,
Extd_Yen_Cost_' + @cnt + ' = 0,
DT_LOAD = getdate()
from Cr_Lg_Sku_Channel_Dollarization
WHERE Logility_flag IN (''Y'', ''H'', ''P'') AND (' + @dept + ' is null or
(' + @dept + ' is not null and DEPT = ' + @dept + '))')

select @cnt = @cnt + 1

End

set @cnt = '1'

WHILE (@cnt <> 19)
BEGIN

--Updating the Extended US / Yen Cost at the SKU level
EXEC('Update Cr_LG_Sku_Channel_Dollarization
Set
Extd_Cost_'+@cnt+' = X.Extd_Cost_'+@cnt + ',
Extd_Yen_Cost_' + @cnt + ' = X.Extd_Yen_Cost_' + @cnt + ',
Splt_Qty_' + @cnt + ' = X.Splt_Qty_' + @cnt + ',
Extd_Retail_' + @cnt + ' = X.Extd_Retail_' + @cnt + ',
Extd_Yen_Retail_' + @cnt + ' = X.Extd_Yen_Retail_' + @cnt + '
FROM Cr_LG_Sku_Channel_Dollarization J INNER JOIN (
SELECT F.DEPT, F.ITEM_ID, S.Channel_Code,
SUM((F.FCST_QTY_' + @cnt + ' * (Cast(F.US_AVG_COST as decimal (18,
2)))
* S.SPLT_FCTR_' + @cnt + ')) AS Extd_Cost_' + @cnt + ',
Case When S.Channel_Code = ''57001''
Then SUM((F.FCST_QTY_' + @cnt + ' * (Cast(F.YEN_AVG_COST as decimal (18,
2)))
* S.SPLT_FCTR_' + @cnt + '))
Else ''0''
End as Extd_Yen_Cost_' + @cnt + ',
ROUND(SUM(F.FCST_QTY_' + @cnt + ' * S.SPLT_FCTR_' + @cnt + '), 2) AS
Splt_Qty_' + @cnt + ',
Case When S.Channel_Code = ''57001''
Then SUM(((F.FCST_QTY_' + @cnt + ' * (Cast(F.Yen_RETAIL_PRC as decimal
(18, 2)))
* S.SPLT_FCTR_' + @cnt + ') * DIS_FCTR_' + @cnt + ') *
Exchange_Rate)
Else SUM((F.FCST_QTY_' + @cnt + ' * (Cast(F.US_RETAIL_PRC as decimal
(18, 2)))
* S.SPLT_FCTR_' + @cnt + ') * DIS_FCTR_' + @cnt + ')
End as Extd_Retail_' + @cnt + ',
Case When S.Channel_Code = ''57001''
Then SUM((F.FCST_QTY_' + @cnt + ' * (Cast(F.Yen_RETAIL_PRC as decimal
(18, 2)))
* S.SPLT_FCTR_' + @cnt + ') * DIS_FCTR_' + @cnt + ')
Else 0
End as Extd_Yen_Retail_' + @cnt + '
FROM cr_YEN_CONVERSION, cr_LG_RSLT_FCST F INNER JOIN cr_CHANNEL C
ON C.Logility_Channel = F.CHANNEL INNER JOIN cr_CHANNEL_SPLITS S ON
C.Channel_Code = S.Channel_Code AND F.DEPT = S.DEPT Inner Join
cr_CHANNEL_DISCOUNTS d On C.Channel_Code = d.Channel_Code AND f.DEPT =
d.DEPT
WHERE (' + @dept + ' is null or
(' + @dept + ' is not null and F.DEPT = ' + @dept + ')) ' + '
GROUP BY F.DEPT, F.ITEM_ID, S.Channel_Code ) X
ON J.Item_Id = X.Item_Id and J.Channel_Code = X.Channel_Code')

Select @cnt = @cnt + 1

END

Was this post helpful to you?

Why should I rate a post?