From CURSOR to SELECT.... with an EXEC

This is an example of my sql query:

Declare cursorA CURSOR FOR
SELECT idA FROM A WHERE ...

OPEN开发者_开发问答 cursorA 
Fetch Next From cursorA Into @my_id

While @@fetch_status <> -1
begin
  if @@fetch_status <> -2
  begin
     INSERT #TEMP_TABLE
     EXEC sp_MyStoredProcedure @my_id
  end
  Fetch Next From cursorA Into @my_id
end

Close      cursorA 
Deallocate cursorA 

How can I transform this with a SELECT, for example like this:

INSERT #TEMP_TABLE
EXEC sp_MyStoredProcedure idA 
FROM A WHERE ...

?

You can't directly query the result set of a stored procedure I'm afraid. Presumably that's why the cursor is there in the first place.

Using cursors is generally thought to be inefficient, but you could replace it with something like this:

-- get IDs
SELECT idA
INTO #tmpIds
FROM A

-- add row numbers and index for query speed (may not be needed if A is small)
ALTER TABLE #tmpIds ADD RowNum int IDENTITY(1,1)
CREATE CLUSTERED INDEX IDX_tmpIDs_RowNum ON #tmpIds(RowNum)

DECLARE @my_id int, @rowNum int, @rowCount int   
SET @rowNum = 1
SELECT @rowCount = COUNT(*) FROM #tmpIds

-- iterate over #tmpIds
While @rowNum <= @rowCount
begin
    SELECT @my_id = idA FROM #tmpIds WHERE RowNum = @RowNum

    INSERT #TEMP_TABLE
    EXEC sp_MyStoredProcedure @my_id

    SET @rowNum = @rowNum + 1
end

Hey There, You Cant do it that way..

But what you can use is a Function and do a Select * from dbo.FunctionName(@Param)

Personally i would avoid using a Cursor.

If it is possible to get your ID, and then use a TempTable and Do your query based on a list of variables in a table and Expanding the Table to meet the same criteria as your Cursor would be better.

But i can see where you might have limitations with the later comment.

What does the StoredProcReturn?

I would do something Like this:

CREATE TABLE @tmp
(
value int
)

INSERT INTO @tmp (value)
SELECT idA FROM A

INSERT INTO #tmpTable
SELECT value1, value2, value3
FROM dbo.function1(@tmp)