将值传递给存储过程中的参数

Question

我有一个超过500的值的列表，每次我必须将这些值传递给下面的存储过程。是否可以动态调用这些值？

RDBMS: SQL Server 2014 

小集合下面@value例子。

declare @valuetable table 
(
value varchar(50) 
) 

insert @valuetable 

select video union 
select audio union 
select hayward union 
select abott union 
select gsk 

代码：

DECLARE @value VARCHAR(24) SET @value = 'video'  
DECLARE @DAYS INT SET @DAYS = -30    


SELECT * INTO #XTP1 FROM (     
SELECT DISTINCT 'START' AS DTT, DATEADD(D,@DAYS,DATEACTIONED) AS DT FROM NEWREPORTS 
WHERE value = @value       
UNION       
SELECT DISTINCT 'CHANGE' AS DTT, DATEACTIONED AS DT FROM NEWREPORTS 
WHERE value = @value       
)r 

感谢

Answer 1

所以，你需要做的是一列添加到＃XTP1存储值名称。那么你可以这样做：

declare @valuetable table 
(
value varchar(50) 
) 

insert @valuetable 

select video union 
select audio union 
select hayward union 
select abott union 
select gsk 

SELECT * INTO #XTP1 FROM (     
SELECT DISTINCT v.value, 'START' AS DTT, DATEADD(D,@DAYS,DATEACTIONED) AS DT 
FROM NEWREPORTS n INNER JOIN @valuetable v ON n.value = v.value       
UNION       
SELECT DISTINCT v.value, 'CHANGE' AS DTT, DATEACTIONED AS DT 
FROM NEWREPORTS n INNER JOIN @valuetable v ON n.value = v.value 
)r 

现在你可以从＃XTP1中一次性检索所有东西。您可能会希望在您的ORDER BY中包含“值”。