存储过程参数包含多个值

Question

我的存储过程参数包含2个以上的值（例如：create stored procedure recentAssetList@recentAssetList=（id1，id2，..）），那么使用这些参数，我如何从表中获取数据？

Answer 1

SQL Server不支持该逻辑。以下是一些选项：

1.将它们拆分为多个参数

create procedure yourProc
    @FirstParam varchar(10),
    @SecondParam varchar(10)
as
    -- etc.
go

如果其中一些参数可能为空，您可以这样做：

create procedure yourProc
    @FirstParam varchar(10) = null,
    @SecondParam varchar(10) = null
as
    select *
    from yourTable
    where
        ((@FirstParam is null) or (SomeCol1 = @FirstParam)) and
        ((@SecondParam is null) or (SomeCol2 = @SecondParam))
go

2.传递只读表

create type yourTableData 
as table
(
    id int not null
)
go
create procedure yourProc
    @yourInput yourTableData readonly
as
    select *
    from yourTable
    where id in
    (
        select id
        from @yourInput
    )
go

Answer 2

您可以使用类似的拆分函数，并在comcatenated字符串中传递值：

CREATE function [dbo].[csv2tbl](@list nvarchar(max), @delimiter nvarchar(10))
returns @res table ([index] int PRIMARY KEY, col nvarchar(max))
AS BEGIN
with tbl_for_csv as
(
select 0 as [index] ,left(@list + @delimiter+@delimiter,charindex(@delimiter,@list + @delimiter+@delimiter) -1)as col,
right(@list + @delimiter+@delimiter,len(@list + @delimiter+@delimiter) - charindex(@delimiter,@list + @delimiter+@delimiter)) as Str
union all
select [index]+1, left(Str,charindex(@delimiter,Str) - 1)as Col,
right(Str,len(Str) - charindex(@delimiter,Str)) from tbl_for_csv
where len(right(Str,len(Str) - charindex(@delimiter,Str))) > 0
)
INSERT @res
select [index], col from tbl_for_csv option (MAXRECURSION 0);
return;
END

在sql2008+中，您可以通过用户定义的表类型变量将值传递给SP（请参见@Shark answer）