将多个数据分配给sql server存储过程中的变量

I want to assign multiple values to a variable from a SELECT statement in a stored procedure. The code goes like this

我想从存储过程中的SELECT语句为变量分配多个值。代码是这样的

DECLARE @ProjectExecutionPlanId INT=NULL

SELECT @ProjectExecutionPlanId = (SELECT [ID] FROM [dbo].[ProjectExecutionPlan] 
                                  WHERE ProjectDetailID=@PID)

@PID in the input to the stored procedure.

@PID在存储过程的输入中。

The SELECT statement returns multiple values. So I am getting error.

SELECT语句返回多个值。所以我收到了错误。

1 个解决方案

#1

This answer is based on your comment:

这个答案基于你的评论:

I want to delete multiple rows from the ProjectExecutionPlanExecution table which is dependent on ProjectExecutionPlan table.There are multiple plans in ProjectExecutionPlan table. So I will get multiple IDs

我想从ProjectExecutionPlanExecution表中删除多行,这些行依赖于ProjectExecutionPlan表.ProjectExecutionPlan表中有多个计划。所以我会得到多个ID

Delete From ProjectExecutionPlanExecution 
Where ProjectExecutionPlanId In (SELECT [ID]
                                 FROM [dbo].[ProjectExecutionPlan] 
                                 WHERE ProjectDetailID=@PID)

Or

Delete pe From ProjectExecutionPlanExecution pe
Join ProjectExecutionPlan p On pe.ProjectExecutionPlanID = p.ID
WHERE p.ProjectDetailID=@PID