Question

MSSQL数据库 ：如何给 存储过程中的 IN() 传递参数，谁能够帮忙修改一些。

CREATE PROCEDURE [dbo].[Stat_MultiplesLoadByUnionAll]
(
@Cells nvarchar(255)
)
AS
BEGIN
declare @UnionTable varchar(8000)

select @UnionTable=isnull(@UnionTable+' union all select '+@Cells+' from ',' select '+@Cells+' from ') +quotename(Name) from sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1 and name in ('View_16','View_17')

--print @UnionTable
exec(@UnionTable)
END
---------------------------------
--帮忙修改 该存储过程：

--实现上述存储过程中的 in() 中的 数据“'View_16','View_17'” 作为参数传递到存储过程。

--注意：in() 中的项不固定大小

Answer 1

中的项不固定大小,应该选一个足够大的值,varchar(1000)
CREATE PROCEDURE [dbo].[Stat_MultiplesLoadByUnionAll]
( @Cells nvarchar(255),@name1 varchar(1000) )
as
begin
if (@name1 <>'' or @name1 is not null)
begin
select @UnionTable=isnull(@UnionTable+' union all select '+@Cells+' from ',' select '+@Cells+' from ) +quotename(Name) from sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1 and name in (' + @name1 =')'
end

end

--注意@name1 传入的格式 是'tab1','tab2','tab3'................ 这样的,才能拼对

Answer 2

CREATE PROCEDURE [dbo].[Stat_MultiplesLoadByUnionAll]
(
@Cells nvarchar(255),
@View_16 varchar(200),
@View_16 varchar(200)
)
AS
BEGIN
declare @UnionTable varchar(8000)

select @UnionTable=isnull(@UnionTable+' union all select '+@Cells+' from ',' select '+@Cells+' from ') +quotename(Name) from sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1 and name in ('''View_16''','''View_17''')

--print @UnionTable
exec(@UnionTable)
END

Answer 3

--假设@param为传入参数，用逗号分隔，如 “View_16,View_17”
--1.考虑通过like来匹配，为了准确性，需要在两端增加逗号
select @UnionTable=isnull(@UnionTable+' union all select '+@Cells+' from ',' select '+@Cells+' from ') +quotename(Name) from sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1 and ','+@param+',' like '%,'+name+'%,'
--即：',View_16,View_17,' like '%,'+name+'%,'

--2 也可以用charindex函数
charindex( ','+name+',' ,','+@param+',' )>0
charindex( ','+name+',' ,',View_16,View_17,')>0