Get/Use the Select value from a Stored Procedure

Question

I have to use a Stored Procedure - that I cannot change/modify. While it is a bit complicated, it can be simplified to be a SELECT statement i.e. with no RETURN or OUTPUT parameter. For the purpose of this discussion assume it to be something like:

SELECT [URL] as imgPath
     FROM [mydatasource].[dbo].[DigitalContent]

I need to execute this Stored Procedure passing in the Row ID (SKU) of each row in a Table. I use a cursor for this as below:

DECLARE @sku varchar(100)
DECLARE @imgPath varchar(500)
DECLARE c CURSOR FOR
SELECT [SKU]
        FROM [mydatasource].[dbo].[PROD_TABLE]

OPEN c
FETCH NEXT FROM c INTO @sku

WHILE @@FETCH_STATUS = 0 BEGIN
    EXEC @imgPath = [mydatasource].[dbo].[getImage] @sku
    --UPDATE PROD_TABLE SET ImgPath=@imgPath WHERE SKU=@sku
    SELECT @imgPath AS ImgPath
    FETCH NEXT FROM c INTO @sku
END
  CLOSE c
  DEALLOCATE c

Unfortunately, the return value @imgPath comes back as 0 i.e. success. This results in 0s being inserted into my PROD_TABLE or dumped on the Console. However, as the getImage Stored Procedure executes, it dumps the correct values of imgPath to the console.

How do I get this correct value (i.e. the result of the SELECT in the Stored Procedure) in the Loop above, so that I can correctly update my PROD_TABLE?

Answer

Thanks to RBarryYoung suggestion, my final code looks like:

DECLARE @sku varchar(100)
DECLARE @imgPath varchar(500)
DECLARE c CURSOR FOR
SELECT [SKU]
FROM [mydatasource].[dbo].[PROD_TABLE]
OPEN c
FETCH NEXT FROM c INTO @sku

WHILE @@FETCH_STATUS = 0 BEGIN
    CREATE TABLE #OutData ( imgPath varchar(500) )
    INSERT INTO #OutData EXEC [mydatasource].[dbo].[getImage] @sku
    --UPDATE PROD_TABLE SET ImgPath=(SELECT * FROM #OutData) WHERE SKU=@sku
    SELECT * FROM #OutData
    DROP TABLE #OutData

    FETCH NEXT FROM c INTO @sku
END
    CLOSE c
    DEALLOCATE c

The performance may not be the best, but at least it works :-).

http://stackoverflow.com/questions/209383/select-columns-from-result-set-of-stored-procedure — mr.Reband, Jul 09 '13 at 15:20
Thank you **mr.Reband** again! I don't like to have to populate a Table Variable for this - but it seems like the only option :-( — O.O., Jul 09 '13 at 16:02

RBarryYoung · Accepted Answer · 2013-07-09T16:20:56.580

First, create a temp table (#OutData) whose definition matches the output dataset being returned by getImage.

Then, change your EXEC .. statement to this:

INSERT INTO #OutData  EXEC [mydatasource].[dbo].[getImage] @sku

Response to the question: "Is it possible to insert the Key/Row ID into the Temp Table, that way I will not have to TRUNCATE it after each loop iteration?"

First, as a general rule you shouldn't use TRUNCATE on #temp tables as there are some obscure locking problems with that. If you need to do that, just DROP and CREATE them again (they're optimized for that anyway).

Secondly, you cannot modify the dataset returned by a stored procedure in any way. Of course once its in the #temp table you can do what you want with it. So you could add a KeyId column to #OutData. Then inside the loop make a second #temp table (#TmpData), and use INSERT..EXEC to dump into that table instead. Then INSERT..SELECT into #OutData by selecting from #TmpData, adding your KeyID column. Finally, DROP TABLE #TmpData as the last statement in your loop.

This should perform fairly well.

Thank you **RBarryYoung**. I hate to be populating a Table Variable every time I run through the loop. — O.O., Jul 09 '13 at 16:07
My Stored Procedure returns only `imgPath` i.e. no Key, and I cannot modify it. Is it possible to insert the Key/Row ID into the Temp Table, that way I will not have to `TRUNCATE` it after each loop iteration. — O.O., Jul 09 '13 at 16:09
Thank you **RBarryYoung**. That worked for me. I used only one Temp Table, created and deleted in the loop to store the result of the Stored Procedure. — O.O., Jul 09 '13 at 18:43

score 1 · Answer 2 · answered Jul 09 '13 at 17:32

Sometimes executing code entirely inside SQL Server can be more difficult than doing so directly client-side, sending multiple queries calling the SProc (ideally batched in a single round-trip) and processing the results there directly.

Otherwise, the INSERT-EXEC method seems the easier if you absolutely can't modify the called procedure. There are a few alternative methods, all with some additional problems, shown here: http://www.sommarskog.se/share_data.html

Get/Use the Select value from a Stored Procedure

Answer

2 Answers2