SQL exexute store procedure with results of a query.

Posted on 2016-08-29
Last Modified: 2016-08-31

How can I execute a store procedure with the results of a sql query. Here is my attempt that I only got half way with.

In the example below I want to take the AssigmentID and execute each value returned to call a stored procedure.

In this example the ids that would return from the first select are:




declare @test table (id int);

insert into @test select AssignmentID from CI_CIAssignments

select * from @test

exec spUpdateAppDeploymentSummaryByCollection @AssignmentID = 16777359
exec spUpdateAppDeploymentSummaryByCollection @AssignmentID = 16777357
exec spUpdateAppDeploymentSummaryByCollection @AssignmentID = 16777361
exec spUpdateAppDeploymentSummaryByCollection @AssignmentID = 16777358
exec spUpdateAppDeploymentSummaryByCollection @AssignmentID = 16777360

Open in new window

Question by:whorsfall
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 52

Assisted Solution

by:Ryan Chong
Ryan Chong earned 250 total points
ID: 41775761
you need to open a cursor.

for examples:

check the syntax at the section: Why Not Just Use a Cursor?

Using Cursor in SQL Server Stored Procedure with example
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 41776504
Can you change the spUpdateAppDeploymentSummaryByCollection to receive a table instead of a value? That way you can send all values one time only so it will perform it all in one single step.

You can also post here the code of that Stored Procedure so we can find an alternative way to do this.

Accepted Solution

Valliappan AN earned 250 total points
ID: 41776630
you could do something like this:

SELECT Identity(INT, 1,1) AS serial, AssignmentID INTO #temp FROM CI_CIAssignments

DECLARE @maxserial INT, @serial INT, @Id INT

SELECT @maxserial=MAX(serial) FROM #test     --start with the highest primary key serial

SET @serial = 1

WHILE @serial > @maxserial                          --loop until no more records
   SELECT @id = id FROM #temp WHERE serial = @serial 
   EXEC spUpdateAppDeploymentSummaryByCollection @AssignmentID = @id
   SET @serial = @serial + 1


Open in new window


Featured Post

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question