Select from stored procedure

Question

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.

Answer 1

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

Answer 2

Using the WHILE approach for a task like this works fine for a very short list of Ids, but it performs very poorly as the list increases. This is due to the cost of creating the temporary memory table holding the Ids, and its heavy querying and manipulation. You're having to go through the source keys multiple times and it grows fast with the list size.

The canonical approach in SQL Server is to use a CURSOR. Unlike the WHILE solution, a CURSOR goes through the source data only once.

Although setting up a CURSOR is unnecessarily arcane and a royal pain, it's not much harder to write than the WHILE solution.

Most of the code for a cursor is pretty much template code.

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

...

-- Declare a new cursor
DECLARE source CURSOR READ_ONLY
FOR
  -- Your source of data here. It can be as complex as you need it to be 
  select dbo.Workers.Id as Id
  from dbo.Workers


-- One or more "loop variables". Here we only want the Id
DECLARE @Id INT

-- Start Template code
OPEN source
FETCH NEXT FROM source INTO @id
WHILE (@@fetch_status <> -1) -- while there is still data in the cursor
BEGIN
  IF (@@fetch_status <> -2) -- skip ghost records that no longer exist (guards against deletions from other connections)
  BEGIN
    -- End Template code


    -- This is where you put what you want to do with your input
    INSERT INTO #tmpXmlreturn
    Exec dbo.GetBranchByWorkerId @Id


  -- Start Template code
  END
END
CLOSE source
DEALLOCATE source
-- End Template code 
-- We're done


SELECT * FROM #tmpXmlreturn