sql 一个变量对应多个值的存储过程写法

之前客户有个需求：做一个bom查询报表，过滤条件：父项物料代码。要求可以进行多个父项物料代码进行查看

解决思路：把存储过程的变量进行拆分，创建一个临时表放置拆分的数据，然后取数据。

代码如下：

alter PROCEDURE bobang_bomselect
@ID nvarchar(500) 
AS
BEGIN    
SET NOCOUNT ON
if @ID='' or @ID is null
begin
set @ID='%'
end 
DECLARE @PointerPrev int
DECLARE @PointerCurr int
DECLARE @TId nvarchar(500) 
SET @PointerPrev = 1
CREATE TABLE #t_info
(
FBillNo nvarchar(50) 
)
WHILE(@PointerPrev <LEN(@ID))
BEGIN
SET @PointerCurr=CharIndex(',',@ID,@PointerPrev)
IF(@PointerCurr>0)
BEGIN
SET @TId = cast(SUBSTRING(@ID,@PointerPrev,@PointerCurr - @PointerPrev)as nvarchar)
insert into #t_info(FBillNo) values(@TId)
SET @PointerPrev = @PointerCurr+1
END
ELSE
BREAK
END
--因为最后一个ID后面没有逗号，所以在跳出循环后获取
SET @TId = cast(substring(@ID,@PointerPrev,LEN(@ID)-@PointerPrev+1) as nvarchar)
insert into #t_info(FBillNo) values(@TId)


select C.FNumber AS 父项物料代码,C.FName AS 父项物料名称,C.FModel AS 父项规格型号,A.FCheckDate AS 建立日期,
B.FEntryID AS 顺序号,D.FNumber AS 子项物料代码,D.FName AS 子项物料名称,d.FModel AS 子项规格型号,
case when d.FErpClsID=1 then '外购' when d.FErpClsID=2 then '自制' else '委外加工' end as 物料属性,
f.FName as 常用单位,b.FQty as 常用用量,case when d.FUseState =341 then '使用' end as 使用状态, 
case when d.FDeleted=0 then '否' else N'是'end as 是否禁用 
from ICBOM A
LEFT JOIN ICBOMChild B ON A.FInterID=B.FInterID
LEFT JOIN t_ICItem C ON A.FItemID=C.FItemID
LEFT JOIN t_ICItem D ON B.FItemID=D.FItemID
LEFT JOIN t_Item f ON B.FUnitID=f.FItemID
where c.FNumber in (SELECT fbillno FROM #t_info) AND A.FUseStatus=1072 or (c.FNumber like @ID and A.FUseStatus=1072)
ORDER BY C.FNumber,FEntryID

DROP TABLE #t_info
SET NOCOUNT OFF    
END
GO

--EXEC bobang_bomselect '2.0002,BG.09.0012-001'

 

 

--期间百度了好几种方式，存留以备后用（开始时错误的把可以过滤的值全部取出来形成一个数组，然后结果就是取全部的数据了）

--把数值取出来用“，”隔开，形成一个数组
declare @s varchar(1000)
set @s=''
SET @s=STUFF((
SELECT ','+cast(C.FNumber as varchar) from ICBOM A
LEFT JOIN t_ICItem C ON A.FItemID=C.FItemID FOR XML PATH('')
),1,1,'')
SELECT @s

--第二种
declare @su varchar(100)
declare @count int
declare @i int --记录条数
set @count = (select count(*) from ICBOM A
LEFT JOIN t_ICItem C ON A.FItemID=C.FItemID)
set @i = 1;
while @i <= @count
begin
select @su=FNumber
from
(select C.FNumber,row_number() over(order by @@servername) as rownum
from ICBOM A
LEFT JOIN t_ICItem C ON A.FItemID=C.FItemID
)B where rownum = @i
set @i = @i + 1
print @su
end

--结果
declare @b varchar(8000)
declare @a varchar(8000)
SET @a=STUFF((
SELECT ','+cast(C.FNumber as varchar) from ICBOM A
LEFT JOIN t_ICItem C ON A.FItemID=C.FItemID FOR XML PATH('')
),1,1,'')
set @b='select C.FNumber AS 父项物料代码,C.FName AS 父项物料名称,C.FModel AS 父项规格型号,A.FCheckDate AS 建立日期,
B.FEntryID AS 顺序号,D.FNumber AS 子项物料代码,D.FName AS 子项物料名称,d.FModel AS 子项规格型号 from ICBOM A
LEFT JOIN ICBOMChild B ON A.FInterID=B.FInterID
LEFT JOIN t_ICItem C ON A.FItemID=C.FItemID
LEFT JOIN t_ICItem D ON B.FItemID=D.FItemID
LEFT JOIN t_Item f ON B.FUnitID=f.FItemID
where C.FNumber in ('''+replace(@a,',',''',''')+''')'
exec (@b)

备注： ('''+replace(@a,',',''',''')+''')'  这边是把数组里的逗号转义为：',' 结果就是：' xxx','xxx','····  '