Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 81
  • Last Modified:

SQL exexute store procedure with results of a query.

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
whorsfall
Asked:
whorsfall
2 Solutions
 
Ryan ChongCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Valliappan ANCommented:
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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now