SQL Server – How to Select from Stored Procedure

sql serverstored-procedures

Hello I have a table called Workers in my database. Also I have a stored procedure that returns a table with Id, xml Data and xml Schema by Worker.Id. I want to select all Worker.Id values and execute this procedure for each Worker.id. Here is some script:

create table #tmpXmlreturn
(
    Id int,
    Data xml,
    [Schema] xml
)

select dbo.Workers.Id as Id, 
        Exec dbo.GetBranchByWorkerId Id,
into #temptable from
dbo.Workers

If you need me to provide more information please tell me.

Best Answer

This should do what you need.

DECLARE @Id INT

create table #tmpXmlreturn
(
    Data xml,
)

create table #Ids(
    Id int
)

INSERT INTO #Ids
select dbo.Workers.Id as Id
from dbo.Workers

WHILE EXISTS(SELECT * FROM #Ids)
BEGIN
  SELECT TOP 1 @Id = Id FROM #Ids  

  INSERT INTO #tmpXmlreturn
  Exec dbo.GetBranchByWorkerId @Id

  DELETE FROM #Ids WHERE Id = @Id
END

SELECT * FROM #tmpXmlreturn
DROP TABLE #tmpXmlreturn
Related Question