Sql – Critique this SQL stored procedure

sqlstored-procedures

below is a massive stored procedure that a contract developer wrote for me, I feel like I am picking on the developer but it is just terrible. What are the main issues you can see with it?

CREATE PROCEDURE [dbo].[usp_SHS_XXXX]  
( 
    @request_identifier varchar(255),
    @category_guids varchar(4000),
    @url varchar(500),
    @section_id int,
    @NoOfDaysToRank int = 45,
    @SaleRankWeight decimal(18,2) = 1.0,
    @QuantityRankWeight decimal(18,2) = 1.0,
    @NewItemWeight decimal(18,2) = 0.2      
)
AS  
BEGIN  
    SET NOCOUNT ON

    declare @tblCatAttrFilteredPhysicalItems table
    (
        [request_identifier] [uniqueidentifier] NULL,
        [item_guid] [uniqueidentifier] NULL,
        [item_cd] [varchar](25) NULL,
        [master_guid] [uniqueidentifier] NULL,
        [item_type_id] [int] NULL,
        [item_description_title] [varchar](100) NULL,
        [item_description_short] [varchar](255) NULL,
        [item_description] [varchar](3000) NULL,
        [item_retail_price] [decimal](18, 2) NULL,
        [item_sale_price] [decimal](18, 2) NULL,
        [item_backorderable] [int] NULL,
        [item_discontinued] [int] NULL,
        [item_available] [int] NULL,
        [item_catalog_guid] [uniqueidentifier] NULL,
        [item_image_counter] [int] NULL,
        [item_color] [varchar](255) NULL,
        [item_gender] [varchar](255) NULL,
        [item_age_group] [varchar](255) NULL,
        [item_price_updated] [varchar](25) NULL,
        [item_licensor] [varchar](255) NULL,
        [item_manufacturer] [varchar](255) NULL,
        [item_primary_license] [varchar](255) NULL,
        [item_series] [varchar](255) NULL,
        [item_size] [varchar](255) NULL,
        [item_associated_hero] [varchar](255) NULL,
        [item_tshirt_attributes] [varchar](255) NULL,
        [item_date_counted] [varchar](25) NULL,
        [item_hide_from_search] [varchar](255) NULL,
        [item_holiday] [varchar](255) NULL,
        [item_material] [varchar](255) NULL,
        [item_newphoto_needed] [varchar](255) NULL,
        [item_sleeve_type] [varchar](255) NULL,
        [item_softness] [varchar](255) NULL,
        [item_created] [datetime] NULL,
        [item_approved] [int] NULL,
        [item_quantity_on_hand] [int] NULL,
        [item_quantity_on_hold] [int] NULL,
        [item_quantity_on_order] [int] NULL,
        [item_weight] [decimal](18,2) NULL,
        [is_item_new] [varchar](5) NULL,
        [category_guid] [varchar](255) NULL,
        [category_name] [varchar](50) NULL,
        [item_quantity] [int],
        [item_price_adjustment_value] [decimal](16,6),
        [item_control_link] [nvarchar](500),
        [item_promotion] [nvarchar](255),
        [item_price_adjustment] [decimal](16,6)
    )

    declare
        @attribute_cd varchar(255),
        @attribute_value varchar(6000),
        @tmpAttrValue varchar(255),
        @first_section_id int;

    declare @primary_license varchar(255);
    select @primary_license = primaryLicense from pageData
    where url = @url;

    declare curSecAttr cursor for
    select attribute_cd,attribute_value from shs_page_section_attributes
    where url = @url and section_id = @section_id;  

    declare @show_only_new char(5);
    select @show_only_new = ISNULL(show_only_new, 'N')
    from shs_page_sections
    where url = @url 
    AND section_id = @section_id;

        insert into @tblCatAttrFilteredPhysicalItems
        select 
            @request_identifier,i.*
        from mf_item_detail i
        inner join mf_item_detail mi
        on i.master_guid = mi.item_guid
        inner join mf_item_categories ic
        on mi.item_guid = ic.item_guid
        where ic.category_guid in (select item from dbo.udfSplit(@category_guids,'#'))
        and mi.item_image_counter > 0
        and mi.item_discontinued = 0
        and mi.item_type_id = 3
        and i.item_quantity > 0

        UNION

        select
            @request_identifier,i.*
        from mf_item_detail i
        inner join mf_item_categories ic
        on i.item_guid = ic.item_guid
        where ic.category_guid in (select item from dbo.udfSplit(@category_guids,'#'))
        and i.item_image_counter > 0
        and i.item_discontinued = 0
        and i.item_type_id = 1
        and i.master_guid IS NULL
        and i.item_quantity > 0;

    select * into #tmpCatAttrFilteredItems from @tblCatAttrFilteredPhysicalItems;

    update #tmpCatAttrFilteredItems
    set item_color = mi.item_color,
    item_gender = mi.item_gender,
    item_holiday = mi.item_holiday,
    item_age_group = mi.item_age_group,
    item_licensor = mi.item_licensor,
    item_manufacturer = mi.item_manufacturer,
    item_material = mi.item_material,
    item_primary_license = mi.item_primary_license,
    item_series = mi.item_series,
    item_sleeve_type = mi.item_sleeve_type,
    item_softness = mi.item_softness,
    item_tshirt_attributes = mi.item_tshirt_attributes,
    item_promotion = mi.item_promotion 
    from mf_item_detail mi
    where mi.item_guid = #tmpCatAttrFilteredItems.master_guid
    and #tmpCatAttrFilteredItems.item_type_id = 1
    and #tmpCatAttrFilteredItems.master_guid is not null
    and request_identifier = @request_identifier;

    select * into #tblTmpResultSet from #tmpCatAttrFilteredItems where 1 = 2;

    open curSecAttr;
    fetch curSecAttr into @attribute_cd,@attribute_value;

    while(@@FETCH_STATUS = 0)
    begin
        if(@attribute_cd = 'AssociatedHero')
        begin
            if (charIndex(',',@attribute_value) > 0)
            begin
                declare cur_AttrValue cursor for
                select item from dbo.udfSplit(@attribute_value,',');

                open cur_AttrValue;
                fetch cur_AttrValue into @tmpAttrValue;
                while (@@FETCH_STATUS = 0)
                begin
                    insert into #tblTmpResultSet
                    select * from #tmpCatAttrFilteredItems
                    where @tmpAttrValue in (select item from dbo.udfSplit(item_associated_hero,','))
                    and request_identifier = @request_identifier;

                    fetch cur_AttrValue into @tmpAttrValue;
                end
                close cur_AttrValue;
                deallocate cur_AttrValue;
            end
            else
            begin
                insert into #tblTmpResultSet
                select * from #tmpCatAttrFilteredItems
                where @attribute_value in (select item from dbo.udfSplit(item_associated_hero,',')) 
                and request_identifier = @request_identifier;
            end

            delete from #tmpCatAttrFilteredItems where request_identifier = @request_identifier;

            insert into #tmpCatAttrFilteredItems
            select * from #tblTmpResultSet where request_identifier = @request_identifier;

            delete from #tblTmpResultSet where request_identifier = @request_identifier;
        end

        if(@attribute_cd = 'Color')
        begin
            if (charIndex(',',@attribute_value) > 0)
            begin
                declare cur_AttrValue cursor for
                select item from dbo.udfSplit(@attribute_value,',');

                open cur_AttrValue;
                fetch cur_AttrValue into @tmpAttrValue;
                while (@@FETCH_STATUS = 0)
                begin
                    insert into #tblTmpResultSet
                    select * from #tmpCatAttrFilteredItems
                    where @tmpAttrValue in (select item from dbo.udfSplit(item_color,','))
                    and request_identifier = @request_identifier;                           

                    fetch cur_AttrValue into @tmpAttrValue;
                end
                close cur_AttrValue;
                deallocate cur_AttrValue;
            end
            else
            begin
                insert into #tblTmpResultSet
                select * from #tmpCatAttrFilteredItems
                where @attribute_value in (select item from dbo.udfSplit(item_color,','))
                and request_identifier = @request_identifier;                           
            end

            delete from #tmpCatAttrFilteredItems where request_identifier = @request_identifier;                            ;

            insert into #tmpCatAttrFilteredItems
            select * from #tblTmpResultSet where request_identifier = @request_identifier;;

            delete from #tblTmpResultSet where request_identifier = @request_identifier;
        end         

        if(@attribute_cd = 'Gender')
        begin
            if (charIndex(',',@attribute_value) > 0)
            begin
                declare cur_AttrValue cursor for
                select item from dbo.udfSplit(@attribute_value,',');

                open cur_AttrValue;
                fetch cur_AttrValue into @tmpAttrValue;
                while (@@FETCH_STATUS = 0)
                begin
                    insert into #tblTmpResultSet
                    select * from #tmpCatAttrFilteredItems
                    where @tmpAttrValue in (select item from dbo.udfSplit(item_gender,','))
                    and request_identifier = @request_identifier;

                    fetch cur_AttrValue into @tmpAttrValue;
                end
                close cur_AttrValue;
                deallocate cur_AttrValue;
            end
            else
            begin
                insert into #tblTmpResultSet
                select * from #tmpCatAttrFilteredItems
                where @attribute_value in (select item from dbo.udfSplit(item_gender,','))
                and request_identifier = @request_identifier;
            end

            delete from #tmpCatAttrFilteredItems where request_identifier = @request_identifier;

            insert into #tmpCatAttrFilteredItems
            select * from #tblTmpResultSet where request_identifier = @request_identifier;

            delete from #tblTmpResultSet where request_identifier = @request_identifier;
        end                 

        if(@attribute_cd = 'Holiday')
        begin
            if (charIndex(',',@attribute_value) > 0)
            begin
                declare cur_AttrValue cursor for
                select item from dbo.udfSplit(@attribute_value,',');

                open cur_AttrValue;
                fetch cur_AttrValue into @tmpAttrValue;
                while (@@FETCH_STATUS = 0)
                begin
                    insert into #tblTmpResultSet
                    select * from #tmpCatAttrFilteredItems
                    where @tmpAttrValue in (select item from dbo.udfSplit(item_holiday,','))
                    and request_identifier = @request_identifier;

                    fetch cur_AttrValue into @tmpAttrValue;
                end
                close cur_AttrValue;
                deallocate cur_AttrValue;
            end
            else
            begin
                insert into #tblTmpResultSet
                select * from #tmpCatAttrFilteredItems
                where @attribute_value in (select item from dbo.udfSplit(item_holiday,','))
                and request_identifier = @request_identifier;
            end

            delete from #tmpCatAttrFilteredItems where request_identifier = @request_identifier;;

            insert into #tmpCatAttrFilteredItems
            select * from #tblTmpResultSet where request_identifier = @request_identifier;

            delete from #tblTmpResultSet where request_identifier = @request_identifier;
        end 

        if(@attribute_cd = 'Promotion')
        begin
              if (charIndex(',',@attribute_value) > 0)
              begin
                    declare cur_AttrValue cursor for
                    select item from dbo.udfSplit(@attribute_value,',');

                    open cur_AttrValue;
                    fetch cur_AttrValue into @tmpAttrValue;
                    while (@@FETCH_STATUS = 0)
                    begin
                          insert into #tblTmpResultSet
                          select * from #tmpCatAttrFilteredItems
                          where @tmpAttrValue in (select item from dbo.udfSplit(item_promotion,','))
                          and request_identifier = @request_identifier;

                          fetch cur_AttrValue into @tmpAttrValue;
                    end
                    close cur_AttrValue;
                    deallocate cur_AttrValue;
              end
              else
              begin
                    insert into #tblTmpResultSet
                    select * from #tmpCatAttrFilteredItems
                    where @attribute_value in (select item from dbo.udfSplit(item_promotion,','))
                    and request_identifier = @request_identifier;
              end

              delete from #tmpCatAttrFilteredItems where request_identifier = @request_identifier;

              insert into #tmpCatAttrFilteredItems
              select * from #tblTmpResultSet where request_identifier = @request_identifier;

              delete from #tblTmpResultSet where request_identifier = @request_identifier;
        end

        if(@attribute_cd = 'IntendedAgeGroup')
        begin
            if (charIndex(',',@attribute_value) > 0)
            begin
                declare cur_AttrValue cursor for
                select item from dbo.udfSplit(@attribute_value,',');

                open cur_AttrValue;
                fetch cur_AttrValue into @tmpAttrValue;
                while (@@FETCH_STATUS = 0)
                begin
                    insert into #tblTmpResultSet
                    select * from #tmpCatAttrFilteredItems
                    where @tmpAttrValue in (select item from dbo.udfSplit(item_age_group,','))
                    and request_identifier = @request_identifier;

                    fetch cur_AttrValue into @tmpAttrValue;
                end
                close cur_AttrValue;
                deallocate cur_AttrValue;
            end
            else
            begin
                insert into #tblTmpResultSet
                select * from #tmpCatAttrFilteredItems
                where @attribute_value in (select item from dbo.udfSplit(item_age_group,','))
                and request_identifier = @request_identifier;
            end

            delete from #tmpCatAttrFilteredItems where request_identifier = @request_identifier;

            insert into #tmpCatAttrFilteredItems
            select * from #tblTmpResultSet where request_identifier = @request_identifier;

            delete from #tblTmpResultSet where request_identifier = @request_identifier;
        end                 

        if(@attribute_cd = 'Licensor')
        begin
            if (charIndex(',',@attribute_value) > 0)
            begin
                declare cur_AttrValue cursor for
                select item from dbo.udfSplit(@attribute_value,',');

                open cur_AttrValue;
                fetch cur_AttrValue into @tmpAttrValue;
                while (@@FETCH_STATUS = 0)
                begin
                    insert into #tblTmpResultSet
                    select * from #tmpCatAttrFilteredItems
                    where @tmpAttrValue in (select item from dbo.udfSplit(item_licensor,','))   
                    and request_identifier = @request_identifier;

                    fetch cur_AttrValue into @tmpAttrValue;
                end
                close cur_AttrValue;
                deallocate cur_AttrValue;
            end
            else
            begin
                insert into #tblTmpResultSet
                select * from #tmpCatAttrFilteredItems
                where @attribute_value in (select item from dbo.udfSplit(item_licensor,','))
                and request_identifier = @request_identifier;
            end

            delete from #tmpCatAttrFilteredItems where request_identifier = @request_identifier;

            insert into #tmpCatAttrFilteredItems
            select * from #tblTmpResultSet where request_identifier = @request_identifier;

            delete from #tblTmpResultSet where request_identifier = @request_identifier;
        end         

        if(@attribute_cd = 'Manufacturer')
        begin
            if (charIndex(',',@attribute_value) > 0)
            begin
                declare cur_AttrValue cursor for
                select item from dbo.udfSplit(@attribute_value,',');

                open cur_AttrValue;
                fetch cur_AttrValue into @tmpAttrValue;
                while (@@FETCH_STATUS = 0)
                begin
                    insert into #tblTmpResultSet
                    select * from #tmpCatAttrFilteredItems
                    where @tmpAttrValue in (select item from dbo.udfSplit(item_manufacturer,','))
                    and request_identifier = @request_identifier;

                    fetch cur_AttrValue into @tmpAttrValue;
                end
                close cur_AttrValue;
                deallocate cur_AttrValue;
            end
            else
            begin
                insert into #tblTmpResultSet
                select * from #tmpCatAttrFilteredItems
                where @attribute_value in (select item from dbo.udfSplit(item_manufacturer,','))
                and request_identifier = @request_identifier;
            end

            delete from #tmpCatAttrFilteredItems where request_identifier = @request_identifier;

            insert into #tmpCatAttrFilteredItems
            select * from #tblTmpResultSet where request_identifier = @request_identifier;

            delete from #tblTmpResultSet where request_identifier = @request_identifier;
        end                 

        if(@attribute_cd = 'Material')
        begin
            if (charIndex(',',@attribute_value) > 0)
            begin
                declare cur_AttrValue cursor for
                select item from dbo.udfSplit(@attribute_value,',');

                open cur_AttrValue;
                fetch cur_AttrValue into @tmpAttrValue;
                while (@@FETCH_STATUS = 0)
                begin
                    insert into #tblTmpResultSet
                    select * from #tmpCatAttrFilteredItems
                    where @tmpAttrValue in (select item from dbo.udfSplit(item_material,','))   
                    and request_identifier = @request_identifier;

                    fetch cur_AttrValue into @tmpAttrValue;
                end
                close cur_AttrValue;
                deallocate cur_AttrValue;
            end
            else
            begin
                insert into #tblTmpResultSet
                select * from #tmpCatAttrFilteredItems
                where @attribute_value in (select item from dbo.udfSplit(item_material,','))
                and request_identifier = @request_identifier;
            end

            delete from #tmpCatAttrFilteredItems where request_identifier = @request_identifier;

            insert into #tmpCatAttrFilteredItems
            select * from #tblTmpResultSet where request_identifier = @request_identifier;

            delete from #tblTmpResultSet where request_identifier = @request_identifier;
        end                         

        if(@attribute_cd = 'PrimaryLicense')
        begin
            if (charIndex(',',@attribute_value) > 0)
            begin
                declare cur_AttrValue cursor for
                select item from dbo.udfSplit(@attribute_value,',');

                open cur_AttrValue;
                fetch cur_AttrValue into @tmpAttrValue;
                while (@@FETCH_STATUS = 0)
                begin
                    insert into #tblTmpResultSet
                    select * from #tmpCatAttrFilteredItems
                    where @tmpAttrValue in (select item from dbo.udfSplit(item_primary_license,','))    
                    and request_identifier = @request_identifier;

                    fetch cur_AttrValue into @tmpAttrValue;
                end
                close cur_AttrValue;
                deallocate cur_AttrValue;
            end
            else
            begin
                insert into #tblTmpResultSet
                select * from #tmpCatAttrFilteredItems
                where @attribute_value in (select item from dbo.udfSplit(item_primary_license,','))
                and request_identifier = @request_identifier;
            end

            delete from #tmpCatAttrFilteredItems where request_identifier = @request_identifier;

            insert into #tmpCatAttrFilteredItems
            select * from #tblTmpResultSet where request_identifier = @request_identifier;

            delete from #tblTmpResultSet where request_identifier = @request_identifier;
        end 

        if(@attribute_cd = 'Series')
        begin
            if (charIndex(',',@attribute_value) > 0)
            begin
                declare cur_AttrValue cursor for
                select item from dbo.udfSplit(@attribute_value,',');

                open cur_AttrValue;
                fetch cur_AttrValue into @tmpAttrValue;
                while (@@FETCH_STATUS = 0)
                begin
                    insert into #tblTmpResultSet
                    select * from #tmpCatAttrFilteredItems
                    where @tmpAttrValue in (select item from dbo.udfSplit(item_series,',')) 
                    and request_identifier = @request_identifier;

                    fetch cur_AttrValue into @tmpAttrValue;
                end
                close cur_AttrValue;
                deallocate cur_AttrValue;
            end
            else
            begin
                insert into #tblTmpResultSet
                select * from #tmpCatAttrFilteredItems
                where @attribute_value in (select item from dbo.udfSplit(item_series,','))
                and request_identifier = @request_identifier;
            end         

            delete from #tmpCatAttrFilteredItems where request_identifier = @request_identifier;

            insert into #tmpCatAttrFilteredItems
            select * from #tblTmpResultSet where request_identifier = @request_identifier;

            delete from #tblTmpResultSet where request_identifier = @request_identifier;
        end     

        if(@attribute_cd = 'Size')
        begin
            if (charIndex(',',@attribute_value) > 0)
            begin
                declare cur_AttrValue cursor for
                select item from dbo.udfSplit(@attribute_value,',');

                open cur_AttrValue;
                fetch cur_AttrValue into @tmpAttrValue;
                while (@@FETCH_STATUS = 0)
                begin
                    insert into #tblTmpResultSet
                    select * from #tmpCatAttrFilteredItems
                    where @tmpAttrValue in (select item from dbo.udfSplit(item_size,','))   
                    and request_identifier = @request_identifier;

                    fetch cur_AttrValue into @tmpAttrValue;
                end
                close cur_AttrValue;
                deallocate cur_AttrValue;
            end
            else
            begin
                insert into #tblTmpResultSet
                select * from #tmpCatAttrFilteredItems
                where @attribute_value in (select item from dbo.udfSplit(item_size,','))
                and request_identifier = @request_identifier;
            end         

            delete from #tmpCatAttrFilteredItems where request_identifier = @request_identifier;

            insert into #tmpCatAttrFilteredItems
            select * from #tblTmpResultSet where request_identifier = @request_identifier;

            delete from #tblTmpResultSet where request_identifier = @request_identifier;
        end             

        if(@attribute_cd = 'SleeveType')
        begin
            if (charIndex(',',@attribute_value) > 0)
            begin
                declare cur_AttrValue cursor for
                select item from dbo.udfSplit(@attribute_value,',');

                open cur_AttrValue;
                fetch cur_AttrValue into @tmpAttrValue;
                while (@@FETCH_STATUS = 0)
                begin
                    insert into #tblTmpResultSet
                    select * from #tmpCatAttrFilteredItems
                    where @tmpAttrValue in (select item from dbo.udfSplit(item_sleeve_type,','))    
                    and request_identifier = @request_identifier;

                    fetch cur_AttrValue into @tmpAttrValue;
                end
                close cur_AttrValue;
                deallocate cur_AttrValue;
            end
            else
            begin
                insert into #tblTmpResultSet
                select * from #tmpCatAttrFilteredItems
                where @attribute_value in (select item from dbo.udfSplit(item_sleeve_type,','))
                and request_identifier = @request_identifier;
            end         

            delete from #tmpCatAttrFilteredItems where request_identifier = @request_identifier;

            insert into #tmpCatAttrFilteredItems
            select * from #tblTmpResultSet where request_identifier = @request_identifier;

            delete from #tblTmpResultSet where request_identifier = @request_identifier;        
        end

        if(@attribute_cd = 'Softness')
        begin
            if (charIndex(',',@attribute_value) > 0)
            begin
                declare cur_AttrValue cursor for
                select item from dbo.udfSplit(@attribute_value,',');

                open cur_AttrValue;
                fetch cur_AttrValue into @tmpAttrValue;
                while (@@FETCH_STATUS = 0)
                begin
                    insert into #tblTmpResultSet
                    select * from #tmpCatAttrFilteredItems
                    where @tmpAttrValue in (select item from dbo.udfSplit(item_softness,','))   
                    and request_identifier = @request_identifier;

                    fetch cur_AttrValue into @tmpAttrValue;
                end
                close cur_AttrValue;
                deallocate cur_AttrValue;
            end
            else
            begin
                insert into #tblTmpResultSet
                select * from #tmpCatAttrFilteredItems
                where @attribute_value in (select item from dbo.udfSplit(item_softness,','))
                and request_identifier = @request_identifier;
            end         

            delete from #tmpCatAttrFilteredItems where request_identifier = @request_identifier;

            insert into #tmpCatAttrFilteredItems
            select * from #tblTmpResultSet where request_identifier = @request_identifier;

            delete from #tblTmpResultSet where request_identifier = @request_identifier;
        end     

        if(@attribute_cd = 'T-ShirtAttributes')
        begin
            if (charIndex(',',@attribute_value) > 0)
            begin
                declare cur_AttrValue cursor for
                select item from dbo.udfSplit(@attribute_value,',');

                open cur_AttrValue;
                fetch cur_AttrValue into @tmpAttrValue;
                while (@@FETCH_STATUS = 0)
                begin
                    insert into #tblTmpResultSet
                    select * from #tmpCatAttrFilteredItems
                    where @tmpAttrValue in (select item from dbo.udfSplit(item_tshirt_attributes,','))
                    and request_identifier = @request_identifier;

                    fetch cur_AttrValue into @tmpAttrValue;
                end
                close cur_AttrValue;
                deallocate cur_AttrValue;
            end
            else
            begin
                insert into #tblTmpResultSet
                select * from #tmpCatAttrFilteredItems
                where @attribute_value in (select item from dbo.udfSplit(item_tshirt_attributes,','))
                and request_identifier = @request_identifier;
            end

            delete from #tmpCatAttrFilteredItems where request_identifier = @request_identifier;

            insert into #tmpCatAttrFilteredItems
            select * from #tblTmpResultSet where request_identifier = @request_identifier;

            delete from #tblTmpResultSet where request_identifier = @request_identifier;
        end     

        fetch curSecAttr into @attribute_cd,@attribute_value;
    end
    close curSecAttr;
    deallocate curSecAttr;

    declare @tblFilteredItems table
    (
        request_identifier uniqueidentifier,
        item_guid varchar(255),
        item_cd varchar(25),
        master_guid varchar(255),
        item_quantity int,
        item_created datetime,
        item_approved int,
        sale_rank decimal(18,2) NULL,
        qoh_rank decimal(18,2) NULL,
        new_item_flag int NULL,
        item_rank decimal(18,2) NULL,
        is_item_new varchar(5)      
    );

    declare @tblFilteredOrderItems table
    (
        request_identifier uniqueidentifier,
        item_guid varchar(255),
        item_cd varchar(25),
        master_guid varchar(255),
        item_quantity int,
        order_item_quantity int,
        order_item_quantity_fulfilled int,
        item_created datetime,
        item_approved int,
        sale_rank decimal(18,2) NULL,
        qoh_rank decimal(18,2) NULL,
        new_item_flag int NULL,
        item_rank decimal(18,2) NULL,
        is_item_new varchar(5)      
    );

    declare @tblFinalResultItems table
    (
        request_identifier uniqueidentifier,
        item_guid varchar(255),
        item_cd varchar(25),
        master_guid varchar(255),
        item_quantity int,
        order_item_quantity int,
        order_item_quantity_fulfilled int,
        item_created datetime,
        item_approved int,
        sale_rank numeric(18,5) NULL,
        qoh_rank numeric(18,5) NULL,
        new_item_flag int NULL,
        item_rank numeric(18,5) NULL,
        is_item_new varchar(5)      
    );  

    declare 
        @TotNoOfItems int,
        @MaxOrderQuantity int,
        @MaxQtyOnHand int;

    insert into @tblFilteredOrderItems
    (request_identifier,item_guid,item_cd,master_guid,item_quantity,order_item_quantity,order_item_quantity_fulfilled,item_created,item_approved,is_item_new)
    select
        distinct @request_identifier,
        oi.item_guid,
        min(i.item_cd) as item_cd,
        i.master_guid as master_guid,
        sum(i.item_quantity) as item_quantity,
        sum(oi.order_item_quantity) as order_item_quantity,
        sum(oi.order_item_quantity_fulfilled) as order_item_quantity_fulfilled,
        MIN(i.item_created) as item_created,
        avg(i.item_approved) as item_approved,
        min(i.is_item_new) as is_item_new
    from mf_order_items oi
    inner join #tmpCatAttrFilteredItems i
    on i.item_guid = oi.item_guid
    inner join mf_orders o
    on o.order_guid = oi.order_guid
    where o.order_date between getdate()-@NoOfDaysToRank and getdate()
    and i.master_guid is NULL
    group by oi.item_guid,i.master_guid

    UNION

    select
        distinct @request_identifier,
        mi.item_guid,
        min(mi.item_cd) as item_cd,
        mi.master_guid as master_guid,
        sum(i.item_quantity) as item_quantity,
        sum(oi.order_item_quantity) as order_item_quantity,
        sum(oi.order_item_quantity_fulfilled) as order_item_quantity_fulfilled,
        MIN(mi.item_created) as item_created,
        min(mi.item_approved) as item_approved,
        min(mi.is_item_new) as is_item_new
    from mf_order_items oi
    inner join #tmpCatAttrFilteredItems i
    on i.item_guid = oi.item_guid 
    inner join mf_item_detail mi
    on mi.item_guid = i.master_guid 
    inner join mf_orders o
    on o.order_guid = oi.order_guid 
    where o.order_date between getdate()-@NoOfDaysToRank and getdate()
    and i.master_guid is NOT NULL   
    group by oi.item_guid,mi.item_guid,mi.master_guid;

    insert into @tblFilteredItems   
    (request_identifier,item_guid,item_cd,master_guid,item_quantity,item_created,item_approved,is_item_new)
    select
        distinct @request_identifier,
        item_guid,
        item_cd as 

Best Answer

Well, just read it very quickly, but 2 things come immediately to mind:

  • Break it down into smaller procedures! This will make maintanence a lot easier.
  • It uses cursors. Maybe there's a way to solve the same things without cursors. IMHO, cursors should be avoided if possible (particularly because cursos go against the set-based approach of SQL Server and because they are damn slow).