Solved

SQL exexute store procedure with results of a query.

Posted on 2016-08-29
3
37 Views
Last Modified: 2016-08-31
Hi,

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:

id
16777359
16777357
16777361
16777358
16777360

Thanks,

ward.


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

0
Comment
Question by:whorsfall
3 Comments
 
LVL 49

Assisted Solution

by:Ryan Chong
Ryan Chong earned 250 total points
Comment Utility
you need to open a cursor.

for examples:

check the syntax at the section: Why Not Just Use a Cursor?
https://www.mssqltips.com/sqlservertip/1414/run-same-command-on-all-sql-server-databases-without-cursors/

Using Cursor in SQL Server Stored Procedure with example
http://www.aspsnippets.com/Articles/Using-Cursor-in-SQL-Server-Stored-Procedure-with-example.aspx
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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.
0
 
LVL 9

Accepted Solution

by:
Valliappan AN earned 250 total points
Comment Utility
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
BEGIN   
   SELECT @id = id FROM #temp WHERE serial = @serial 
   
   EXEC spUpdateAppDeploymentSummaryByCollection @AssignmentID = @id
   
   SET @serial = @serial + 1
END

DROP TABLE #temp
Go

Open in new window

0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

744 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now