Wow... lots of RBAR there 😀
For starters, you can replace the following WHILE loop...
declare @cnt int
declare @UnitCount int
set @cnt=1
set @UnitCount=1
while @cnt<len(@s_Table_PkgNumber)
begin
if(substring(@s_Table_PkgNumber,@cnt,1)=', ')
begin
set @UnitCount=@UnitCount+1
end
set @cnt=@cnt+1
End
... with this...
SET @UnitCount = LEN(@s_Table_PkgNumber)-LEN(REPLACE(@s_Table_PkgNumber,',',''))+1
That one is really important because once you've fixed that, column can be produced just using a simple select which means you can convert the Cursor into a simple SELECT. That means that you can very easily do INSERT/SELECTS instead of using a cursor and things will be very much faster.
The following...
if @s_Quote_Type=' '
set @s_Quote_Type=Null
... can simply be replace by ...
SET @s_Quote_Type = NULLIF(@s_Quote_Type,' ')
I'm on my way to work and I'll try to take a look at this in more detail tonight. In the mean time... experiment with the code suggestions I've made so far...
--Jeff Moden
Change is inevitable... Change for the better is not.