获取/使用存储过程中的“选择”值

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语句,即没有RETURN或OUTPUT参数。出于本讨论的目的,假设它是这样的:

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:

我需要执行此存储过程传递表中每行的行ID(SKU)。我使用光标如下:

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.

不幸的是,返回值@imgPath回到0即成功。这会导致0被插入我的PROD_TABLE或转储到控制台上。但是,当getImage存储过程执行时,它会将正确的imgPath值转储到控制台。

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?

如何在上面的循环中获得这个正确的值(即存储过程中SELECT的结果),以便我可以正确更新我的PROD_TABLE?

Answer

Thanks to RBarryYoung suggestion, my final code looks like:

感谢RBarryYoung的建议,我的最终代码如下:

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 :-).

性能可能不是最好的,但至少它的工作原理:-)。

2 个解决方案

#1

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

首先,创建一个临时表(#OutData),其定义与getImage返回的输出数据集相匹配。

Then, change your EXEC .. statement to this:

然后,将您的EXEC ..语句更改为:

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?"

对问题的回答:“是否可以将键/行ID插入临时表,这样我就不必在每次循环迭代后对其进行TRUNCATE?”

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).

首先,作为一般规则,你不应该在#temp表上使用TRUNCATE,因为它存在一些模糊的锁定问题。如果你需要这样做,只需再次DROP并创建它们(无论如何它们都是优化的)。

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.

其次,您无法以任何方式修改存储过程返回的数据集。当然,一旦它在#temp表中,你可以用它做你想做的事。因此,您可以将KeyId列添加到#OutData。然后在循环内部创建第二个#temp表(#TmpData),并使用INSERT..EXEC转储到该表中。然后通过从#TmpData中选择INSERT..SELECT到#OutData,添加您的KeyID列。最后,DROP TABLE #TmpData作为循环中的最后一个语句。

This should perform fairly well.

这应该表现得相当好。

#2

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.

有时在SQL Server中完全执行代码比直接在客户端执行代码更困难,发送多个查询调用SProc(理想情况下在单个往返中进行批处理)并直接处理结果。

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

否则,如果绝对无法修改被调用的过程,则INSERT-EXEC方法似乎更容易。有一些替代方法,都有一些其他问题,如下所示:http://www.sommarskog.se/share_data.html

#1