Stored Procedure, multiple inputs for parameter

1

Is there a way for me to be able to enter multiple @itemno at once against this stored procedure?

ALTER PROCEDURE [dbo].[IndentedBOM]
-- Add the parameters for the stored procedure here
@itemno varchar(15)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
select bom.[Level],bom.seq_no, bom.Comp,bom.item_desc_1,bom.loc,bom.low_lvl_cd as LLC,bom.attaching_oper_no as OperNo,bom.mfg_uom as UOM,

bom.scrap_factor as Scrap,bom.activity_cd as Active,bom.stocked_fg as STK, bom.bulk_issue_fg as BI, bom.pur_or_mfg as PorM,bom.controlled_fg as CTL,
bom.backflush_fg as BF, bom.Qty, bom.country_origin, bom.std_cost, bom.extra_1
from
(
select distinct bom.*
from QIVIndentedWork _0

left join (select item_no, comp_item_no as Comp,item_desc_1,loc, low_lvl_cd, attaching_oper_no,mfg_uom,scrap_factor,activity_cd,stocked_fg,bulk_issue_fg,pur_or_mfg, controlled_fg,backflush_fg, qty_per_par  as Qty, seq_no, country_origin, std_cost, extra_1 from QIVIndentedWork) _1 on _0.comp_item_no = _1.item_no
left join (select item_no, comp_item_no as Comp,item_desc_1, loc,low_lvl_cd, attaching_oper_no,mfg_uom,scrap_factor,activity_cd,stocked_fg,bulk_issue_fg,pur_or_mfg, controlled_fg,backflush_fg, qty_per_par  as Qty, seq_no, country_origin, std_cost, extra_1 from QIVIndentedWork) _2 on _1.Comp = _2.item_no
left join (select item_no, comp_item_no as Comp,item_desc_1,loc, low_lvl_cd, attaching_oper_no,mfg_uom,scrap_factor,activity_cd,stocked_fg,bulk_issue_fg,pur_or_mfg, controlled_fg,backflush_fg, qty_per_par  as Qty, seq_no, country_origin, std_cost, extra_1 from QIVINDENTEDWORK) _3 on _2.Comp = _3.item_no
left join (select item_no, comp_item_no as Comp,item_desc_1,loc, low_lvl_cd, attaching_oper_no,mfg_uom,scrap_factor,activity_cd,stocked_fg,bulk_issue_fg,pur_or_mfg, controlled_fg,backflush_fg, qty_per_par  as Qty, seq_no, country_origin, std_cost, extra_1 from QIVINDENTEDWORK) _4 on _3.Comp = _4.item_no
left join (select item_no, comp_item_no as Comp,item_desc_1,loc, low_lvl_cd, attaching_oper_no,mfg_uom,scrap_factor,activity_cd,stocked_fg,bulk_issue_fg,pur_or_mfg, controlled_fg,backflush_fg, qty_per_par  as Qty, seq_no, country_origin, std_cost, extra_1 from QIVINDENTEDWORK) _5 on _4.Comp = _5.item_no
left join (select item_no, comp_item_no as Comp,item_desc_1,loc, low_lvl_cd, attaching_oper_no,mfg_uom,scrap_factor,activity_cd,stocked_fg,bulk_issue_fg,pur_or_mfg, controlled_fg,backflush_fg, qty_per_par  as Qty, seq_no, country_origin, std_cost, extra_1 from QIVINDENTEDWORK) _6 on _5.Comp = _6.item_no
left join (select item_no, comp_item_no as Comp,item_desc_1,loc, low_lvl_cd, attaching_oper_no,mfg_uom,scrap_factor,activity_cd,stocked_fg,bulk_issue_fg,pur_or_mfg, controlled_fg,backflush_fg, qty_per_par  as Qty, seq_no, country_origin, std_cost, extra_1 from QIVINDENTEDWORK) _7 on _6.Comp = _7.item_no
left join (select item_no, comp_item_no as Comp,item_desc_1,loc, low_lvl_cd, attaching_oper_no,mfg_uom,scrap_factor,activity_cd,stocked_fg,bulk_issue_fg,pur_or_mfg, controlled_fg,backflush_fg, qty_per_par  as Qty, seq_no, country_origin, std_cost, extra_1 from QIVINDENTEDWORK) _8 on _7.Comp = _8.item_no
left join (select item_no, comp_item_no as Comp, item_desc_1,loc, low_lvl_cd, attaching_oper_no,mfg_uom,scrap_factor,activity_cd,stocked_fg,bulk_issue_fg,pur_or_mfg, controlled_fg,backflush_fg,qty_per_par  as Qty, seq_no, country_origin, std_cost, extra_1 from QIVINDENTEDWORK) _9 on _8.Comp = _9.item_no
left join (select item_no, comp_item_no as Comp,item_desc_1,loc, low_lvl_cd, attaching_oper_no,mfg_uom,scrap_factor,activity_cd,stocked_fg,bulk_issue_fg,pur_or_mfg, controlled_fg,backflush_fg, qty_per_par  as Qty, seq_no, country_origin, std_cost, extra_1 from QIVINDENTEDWORK) _10 on _9.Comp = _10.item_no
left join (select item_no, comp_item_no as Comp,item_desc_1,loc, low_lvl_cd, attaching_oper_no,mfg_uom,scrap_factor,activity_cd,stocked_fg,bulk_issue_fg,pur_or_mfg, controlled_fg,backflush_fg, qty_per_par  as Qty, seq_no, country_origin, std_cost, extra_1 from QIVINDENTEDWORK) _11 on _10.Comp = _11.item_no
left join (select item_no, comp_item_no as Comp,item_desc_1,loc, low_lvl_cd, attaching_oper_no,mfg_uom,scrap_factor,activity_cd,stocked_fg,bulk_issue_fg,pur_or_mfg, controlled_fg,backflush_fg, qty_per_par  as Qty, seq_no, country_origin, std_cost, extra_1 from QIVINDENTEDWORK) _12 on _11.Comp = _12.item_no

cross apply (
select right('000' + cast(_0.seq_no as varchar(3)), 3) As _0
, right('000' + cast(_1.seq_no as varchar(3)), 3) As _1
, right('000' + cast(_2.seq_no as varchar(3)), 3) As _2
, right('000' + cast(_3.seq_no as varchar(3)), 3) As _3
, right('000' + cast(_4.seq_no as varchar(3)), 3) As _4
, right('000' + cast(_5.seq_no as varchar(3)), 3) As _5
, right('000' + cast(_6.seq_no as varchar(3)), 3) As _6
, right('000' + cast(_7.seq_no as varchar(3)), 3) As _7
, right('000' + cast(_8.seq_no as varchar(3)), 3) As _8
, right('000' + cast(_9.seq_no as varchar(3)), 3) As _9
, right('000' + cast(_10.seq_no as varchar(3)), 3) As _10
, right('000' + cast(_11.seq_no as varchar(3)), 3) As _11

) seq


cross apply (
	select distinct * from 
	(
		select 1 as [Level], _0.comp_item_no as Comp,_0.item_desc_1,_0.loc, _0.low_lvl_cd, _0.attaching_oper_no,_0.mfg_uom,_0.scrap_factor,_0.activity_cd,_0.stocked_fg,_0.bulk_issue_fg,_0.pur_or_mfg, _0.controlled_fg,_0.backflush_fg, _0.qty_per_par as Qty,_0.country_origin,_0.std_cost,_0.extra_1, _0.seq_no, seq._0 + _0.comp_item_no as OrderBY
		union all 
		select 2, _1.Comp,_1.item_desc_1,_1.loc, _1.low_lvl_cd, _1.attaching_oper_no,_1.mfg_uom,_1.scrap_factor,_1.activity_cd,_1.stocked_fg,_1.bulk_issue_fg,_1.pur_or_mfg, _1.controlled_fg,_1.backflush_fg, _1.Qty,_1.country_origin,_1.std_cost,_1.extra_1,  _1.seq_no, seq._0 + _0.comp_item_no + seq._1 + _1.Comp
		union all 
		select 3, _2.Comp,_2.item_desc_1,_2.loc, _2.low_lvl_cd, _2.attaching_oper_no,_2.mfg_uom,_2.scrap_factor,_2.activity_cd,_2.stocked_fg,_2.bulk_issue_fg,_2.pur_or_mfg, _2.controlled_fg,_2.backflush_fg, _2.Qty,_2.country_origin,_2.std_cost, _2.extra_1, _2.seq_no, seq._0 + _0.comp_item_no + seq._1 + _1.Comp + seq._2 + _2.Comp
		union all 
		select 4, _3.Comp,_3.item_desc_1,_3.loc, _3.low_lvl_cd, _3.attaching_oper_no,_3.mfg_uom,_3.scrap_factor,_3.activity_cd,_3.stocked_fg,_3.bulk_issue_fg,_3.pur_or_mfg, _3.controlled_fg,_3.backflush_fg, _3.Qty,_3.country_origin,_3.std_cost,_3.extra_1, _3.seq_no, seq._0 + _0.comp_item_no + seq._1 + _1.Comp + seq._2 + _2.Comp + seq._3 + _3.Comp 
		union all 
		select 5, _4.Comp,_4.item_desc_1,_4.loc, _4.low_lvl_cd, _4.attaching_oper_no,_4.mfg_uom,_4.scrap_factor,_4.activity_cd,_4.stocked_fg,_4.bulk_issue_fg,_4.pur_or_mfg, _4.controlled_fg,_4.backflush_fg, _4.Qty,_4.country_origin,_4.std_cost,_4.extra_1, _4.seq_no, seq._0 + _0.comp_item_no + seq._1 + _1.Comp + seq._2 + _2.Comp + seq._3 + _3.Comp + seq._4 + _4.Comp
		union all 
		select 6, _5.Comp,_5.item_desc_1,_5.loc, _5.low_lvl_cd, _5.attaching_oper_no,_5.mfg_uom,_5.scrap_factor,_5.activity_cd,_5.stocked_fg,_5.bulk_issue_fg,_5.pur_or_mfg, _5.controlled_fg,_5.backflush_fg, _5.Qty,_5.country_origin,_5.std_cost,_5.extra_1, _5.seq_no, seq._0 + _0.comp_item_no + seq._1 + _1.Comp + seq._2 + _2.Comp + seq._3 + _3.Comp + seq._4 + _4.Comp + seq._5 + _5.Comp
		union all 
		select 7, _6.Comp,_6.item_desc_1,_6.loc, _6.low_lvl_cd, _6.attaching_oper_no,_6.mfg_uom,_6.scrap_factor,_6.activity_cd,_6.stocked_fg,_6.bulk_issue_fg,_6.pur_or_mfg, _6.controlled_fg,_6.backflush_fg, _6.Qty,_6.country_origin,_6.std_cost,_6.extra_1, _6.seq_no, seq._0 + _0.comp_item_no + seq._1 + _1.Comp + seq._2 + _2.Comp + seq._3 + _3.Comp + seq._4 + _4.Comp + seq._5 + _5.Comp + seq._6 + _6.Comp
	    union all 
		select 8, _7.Comp,_7.item_desc_1,_7.loc, _7.low_lvl_cd, _7.attaching_oper_no,_7.mfg_uom,_7.scrap_factor,_7.activity_cd,_7.stocked_fg,_7.bulk_issue_fg,_7.pur_or_mfg, _7.controlled_fg,_7.backflush_fg, _7.Qty,_7.country_origin,_7.std_cost,_7.extra_1, _7.seq_no, seq._0 + _0.comp_item_no + seq._1 + _1.Comp + seq._2 + _2.Comp + seq._3 + _3.Comp + seq._4 + _4.Comp + seq._5 + _5.Comp + seq._6 + _6.Comp+ seq._7 + _7.Comp
	    union all 
		select 9, _8.Comp,_8.item_desc_1,_8.loc, _8.low_lvl_cd, _8.attaching_oper_no,_8.mfg_uom,_8.scrap_factor,_8.activity_cd,_8.stocked_fg,_8.bulk_issue_fg,_8.pur_or_mfg, _8.controlled_fg,_8.backflush_fg, _8.Qty,_8.country_origin,_8.std_cost,_8.extra_1,	 _8.seq_no, seq._0 + _0.comp_item_no + seq._1 + _1.Comp + seq._2 + _2.Comp + seq._3 + _3.Comp + seq._4 + _4.Comp + seq._5 + _5.Comp + seq._6 + _6.Comp+ seq._7 + _7.Comp + seq._8 + _8.Comp
	    union all 
		select 10, _9.Comp,_9.item_desc_1,_9.loc, _9.low_lvl_cd, _9.attaching_oper_no,_9.mfg_uom,_9.scrap_factor,_9.activity_cd,_9.stocked_fg,_9.bulk_issue_fg,_9.pur_or_mfg, _9.controlled_fg,_9.backflush_fg, _9.Qty,_9.country_origin,_9.std_cost,_9.extra_1, _9.seq_no, seq._0 + _0.comp_item_no + seq._1 + _1.Comp + seq._2 + _2.Comp + seq._3 + _3.Comp + seq._4 + _4.Comp + seq._5 + _5.Comp + seq._6 + _6.Comp+ seq._7 + _7.Comp + seq._8 + _8.Comp + seq._9 + _9.Comp
	    union all 
		select 11, _10.Comp,_10.item_desc_1,_10.loc, _10.low_lvl_cd, _10.attaching_oper_no,_10.mfg_uom,_10.scrap_factor,_10.activity_cd,_10.stocked_fg,_10.bulk_issue_fg,_10.pur_or_mfg, _10.controlled_fg,_10.backflush_fg, _10.Qty,_10.country_origin,_10.std_cost,_10.extra_1, _10.seq_no, seq._0 + _0.comp_item_no + seq._1 + _1.Comp + seq._2 + _2.Comp + seq._3 + _3.Comp + seq._4 + _4.Comp + seq._5 + _5.Comp + seq._6 + _6.Comp+ seq._7 + _7.Comp + seq._8 + _8.Comp + seq._9 + _9.Comp + seq._10 + _10.Comp
		union all 
		select 12, _11.Comp,_11.item_desc_1,_11.loc, _11.low_lvl_cd, _11.attaching_oper_no,_11.mfg_uom,_11.scrap_factor,_11.activity_cd,_11.stocked_fg,_11.bulk_issue_fg,_11.pur_or_mfg, _11.controlled_fg,_11.backflush_fg, _11.Qty,_11.country_origin,_11.std_cost,_11.extra_1, _11.seq_no, seq._0 + _0.comp_item_no + seq._1 + _1.Comp + seq._2 + _2.Comp + seq._3 + _3.Comp + seq._4 + _4.Comp + seq._5 + _5.Comp + seq._6 + _6.Comp+ seq._7 + _7.Comp + seq._8 + _8.Comp + seq._9 + _9.Comp + seq._10 + _10.Comp + seq._11 + _11.Comp
	
	) bom
) bom

where _0.item_no = @itemno and bom.Comp is not null

) bom
order By bom.OrderBY

END

2

The way I normally tackle that is to provide a VARCHAR @parameter to the Sproc, and pass a delimited-list of the ItemNo's that I want to search for. I then "split" that delimited list into a temporary table (i.e. one row for each ItemNo) and then JOIN that temporary table into the main query

The delimiter needs to be something which will not appear within the ItemNo. A comma will do, particularly if ItemNo is numeric, but we tend to use CHAR(254) as being a character that is very hard to enter from the keyboard! and thus almost impossible to appear in the actual data.

3

I use a temp table for that. The temp table is more flexible, too, in that it can handle multiple params rather than just one.

You would then "tell" the stored proc to use a temp table by passing a '#' (or whatever else you want to use) as the @itemno value.

For example:

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
ALTER PROCEDURE [dbo].[IndentedBOM]
@itemno varchar(15)
AS
BEGIN
SET NOCOUNT ON;

--A temp table named #IndentedBOM_Params is used for matching
--itemnos in this proc. The caller of the proc must EITHER:
--1) create the temp table, load it AND pass '#' as @itemno; OR
--2) pass anything other than '#' as the @itemno.

IF OBJECT_ID('tempdb.dbo.#IndentedBOM_Params') IS NULL
BEGIN
CREATE TABLE #IndentedBOM_Params ( itemno varchar(15) PRIMARY KEY )
END /IF/

IF @itemno <> '#'
BEGIN
INSERT INTO #IndentedBOM_Params VALUES(@itemno)
END /IF/

select bom.[Level],bom.seq_no, bom.Comp,bom.item_desc_1,bom.loc,bom.low_lvl_cd as
...
) bom

where _0.item_no in (select itemno from #IndentedBOM_Params) and bom.Comp is not null
) bom
order By bom.OrderBY

END /PROCEDURE/

4

Good point. I too would use a #TEMP table between Sprocs / within some SQL code, but from our APP passing a "table" is a pain (legacy crap :frowning: ), hence the delimited list for us [from APP] , but in more modern apps passing a Table Variable, from the APP, would be another method - and have the benefit that Scott mentioned of also allowing multiple parameters.

5

I inserted the code but it doesn't seem to work for multiple items.

If I enter ABC it works.

If I enter ABC, XYZ nothing is returned.

USE [001]
GO
/****** Object: StoredProcedure [dbo].[IndentedBOM] Script Date: 11/22/2016 1:35:04 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[IndentedBOM]
-- Add the parameters for the stored procedure here
@itemno varchar(365)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--A temp table named #IndentedBOM_Params is used for matching
--itemnos in this proc. The caller of the proc must EITHER:
--1) create the temp table, load it AND pass '#' as @itemno; OR
--2) pass anything other than '#' as the @itemno.
IF OBJECT_ID('tempdb.dbo.#IndentedBOM_Params') IS NULL
BEGIN
CREATE TABLE #IndentedBOM_Params ( itemno varchar(365) PRIMARY KEY )
END /IF/
IF @itemno <> '#'
BEGIN
INSERT INTO #IndentedBOM_Params VALUES(@itemno)
END /IF/

-- Insert statements for procedure here
select bom.[Level],bom.SequenceNum, bom.Comp, bom.Warehouse, bom.Qty

from
(
select distinct bom.*
from QIVIndentedWork _0

left join (select ParentPart, Component as Comp, Warehouse, QtyPer as Qty, SequenceNum from QIVIndentedWork) _1 on _0.Component = _1.ParentPart
left join (select ParentPart, Component as Comp, Warehouse, QtyPer as Qty, SequenceNum from QIVIndentedWork) _2 on _1.Comp = _2.ParentPart
left join (select ParentPart, Component as Comp, Warehouse, QtyPer  as Qty, SequenceNum from QIVIndentedWork) _3 on _2.Comp = _3.ParentPart
left join (select ParentPart, Component as Comp, Warehouse, QtyPer  as Qty, SequenceNum from QIVIndentedWork) _4 on _3.Comp = _4.ParentPart
left join (select ParentPart, Component as Comp, Warehouse, QtyPer  as Qty, SequenceNum from QIVIndentedWork) _5 on _4.Comp = _5.ParentPart
left join (select ParentPart, Component as Comp, Warehouse, QtyPer  as Qty, SequenceNum from QIVIndentedWork) _6 on _5.Comp = _6.ParentPart
left join (select ParentPart, Component as Comp, Warehouse, QtyPer  as Qty, SequenceNum from QIVIndentedWork) _7 on _6.Comp = _7.ParentPart
left join (select ParentPart, Component as Comp, Warehouse, QtyPer  as Qty, SequenceNum from QIVIndentedWork) _8 on _7.Comp = _8.ParentPart
left join (select ParentPart, Component as Comp, Warehouse, QtyPer  as Qty, SequenceNum from QIVIndentedWork) _9 on _8.Comp = _9.ParentPart
left join (select ParentPart, Component as Comp, Warehouse, QtyPer  as Qty, SequenceNum from QIVIndentedWork) _10 on _9.Comp = _10.ParentPart
left join (select ParentPart, Component as Comp, Warehouse, QtyPer  as Qty, SequenceNum from QIVIndentedWork) _11 on _10.Comp = _11.ParentPart
left join (select ParentPart, Component as Comp, Warehouse, QtyPer  as Qty, SequenceNum from QIVIndentedWork) _12 on _11.Comp = _12.ParentPart

cross apply (
select right('000' + cast(_0.SequenceNum as varchar(3)), 3) As _0
, right('000' + cast(_1.SequenceNum as varchar(3)), 3) As _1
, right('000' + cast(_2.SequenceNum as varchar(3)), 3) As _2
, right('000' + cast(_3.SequenceNum as varchar(3)), 3) As _3
, right('000' + cast(_4.SequenceNum as varchar(3)), 3) As _4
, right('000' + cast(_5.SequenceNum as varchar(3)), 3) As _5
, right('000' + cast(_6.SequenceNum as varchar(3)), 3) As _6
, right('000' + cast(_7.SequenceNum as varchar(3)), 3) As _7
, right('000' + cast(_8.SequenceNum as varchar(3)), 3) As _8
, right('000' + cast(_9.SequenceNum as varchar(3)), 3) As _9
, right('000' + cast(_10.SequenceNum as varchar(3)), 3) As _10
, right('000' + cast(_11.SequenceNum as varchar(3)), 3) As _11

) seq


cross apply (
	select distinct * from 
	(
		select 1 as [Level], _0.Component as Comp, _0.Warehouse,   _0.QtyPer as Qty, _0.SequenceNum, seq._0 + _0.Component as OrderBY
		union all 
		select 2, _1.Comp, _1.Warehouse,   _1.Qty, _1.SequenceNum, seq._0 + _0.Component + seq._1 + _1.Comp
		union all 
		select 3, _2.Comp, _2.Warehouse,   _2.Qty, _2.SequenceNum, seq._0 + _0.Component + seq._1 + _1.Comp + seq._2 + _2.Comp
		union all 
		select 4, _3.Comp, _3.Warehouse, _3.Qty, _3.SequenceNum, seq._0 + _0.Component + seq._1 + _1.Comp + seq._2 + _2.Comp + seq._3 + _3.Comp 
		union all 
		select 5, _4.Comp, _4.Warehouse,   _4.Qty, _4.SequenceNum, seq._0 + _0.Component + seq._1 + _1.Comp + seq._2 + _2.Comp + seq._3 + _3.Comp + seq._4 + _4.Comp
		union all 
		select 6, _5.Comp, _5.Warehouse,   _5.Qty, _5.SequenceNum, seq._0 + _0.Component + seq._1 + _1.Comp + seq._2 + _2.Comp + seq._3 + _3.Comp + seq._4 + _4.Comp + seq._5 + _5.Comp
		union all 
		select 7, _6.Comp, _6.Warehouse,    _6.Qty, _6.SequenceNum, seq._0 + _0.Component + seq._1 + _1.Comp + seq._2 + _2.Comp + seq._3 + _3.Comp + seq._4 + _4.Comp + seq._5 + _5.Comp + seq._6 + _6.Comp
	    union all 
		select 8, _7.Comp, _7.Warehouse,    _7.Qty, _7.SequenceNum, seq._0 + _0.Component + seq._1 + _1.Comp + seq._2 + _2.Comp + seq._3 + _3.Comp + seq._4 + _4.Comp + seq._5 + _5.Comp + seq._6 + _6.Comp+ seq._7 + _7.Comp
	    union all 
		select 9, _8.Comp, _8.Warehouse,    _8.Qty, _8.SequenceNum, seq._0 + _0.Component + seq._1 + _1.Comp + seq._2 + _2.Comp + seq._3 + _3.Comp + seq._4 + _4.Comp + seq._5 + _5.Comp + seq._6 + _6.Comp+ seq._7 + _7.Comp + seq._8 + _8.Comp
	    union all 
		select 10, _9.Comp, _9.Warehouse,   _9.Qty, _9.SequenceNum, seq._0 + _0.Component + seq._1 + _1.Comp + seq._2 + _2.Comp + seq._3 + _3.Comp + seq._4 + _4.Comp + seq._5 + _5.Comp + seq._6 + _6.Comp+ seq._7 + _7.Comp + seq._8 + _8.Comp + seq._9 + _9.Comp
	    union all 
		select 11, _10.Comp, _10.Warehouse, _10.Qty, _10.SequenceNum, seq._0 + _0.Component + seq._1 + _1.Comp + seq._2 + _2.Comp + seq._3 + _3.Comp + seq._4 + _4.Comp + seq._5 + _5.Comp + seq._6 + _6.Comp+ seq._7 + _7.Comp + seq._8 + _8.Comp + seq._9 + _9.Comp + seq._10 + _10.Comp
		union all 
		select 12, _11.Comp, _11.Warehouse, _11.Qty, _11.SequenceNum, seq._0 + _0.Component + seq._1 + _1.Comp + seq._2 + _2.Comp + seq._3 + _3.Comp + seq._4 + _4.Comp + seq._5 + _5.Comp + seq._6 + _6.Comp+ seq._7 + _7.Comp + seq._8 + _8.Comp + seq._9 + _9.Comp + seq._10 + _10.Comp + seq._11 + _11.Comp
	
	) bom
) bom

where _0.ParentPart = @itemno and bom.Comp is not null

) bom
order By bom.OrderBY

END

6

You didn't change the WHERE condition. In my original post, I bolded the change that needed to be made to the WHERE clause.

7

You have to either:

Create and Preload a #IndentedBOM_Params table with individual rows for ABC and XYZ
or
SPLIT the @itemno into multiple rows (base don your delimiter character), into a local #TEMP table in the SProc