Assign multiple data to a variable in sql server stored procedure

Question

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

DECLARE @ProjectExecutionPlanId INT=NULL

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

@PID in the input to the stored procedure.

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

which of those multiple values do you want to set in @ProjectExecutionPlanId — ughai
– ughai, Commented Apr 16, 2015 at 10:53
What is the error? Perhaps you need to add TOP 1 to the inner select, or try and understand why you are getting multiple values. — Peter Smith
– Peter Smith, Commented Apr 16, 2015 at 10:58
I can't add TOP 1.I need all the IDs from ProjectExecutionPlan table — Nishanth Bhat
– Nishanth Bhat, Commented Apr 16, 2015 at 11:01

Giorgi Nakeuri · Accepted Answer · 2015-04-16 11:56:33Z

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

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

edited Apr 16, 2015 at 11:56

answered Apr 16, 2015 at 11:50

Giorgi Nakeuri

35.9k8 gold badges50 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Assign multiple data to a variable in sql server stored procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related