SQL Server 2016中SELECT/函数如何执行带参数存储过程

你编写了存储过程SP_Integraciones_AsignarLotesLineas，入参为@Cantidad、@producto、@bodega，用于按入库日期先后顺序扣减指定商品、指定仓库的批次库存，返回扣减匹配的批次明细。由于SQL Server函数内不允许执行DML语句，因此采用存储过程实现该库存分配逻辑，存储过程代码如下：

CREATE OR ALTER PROCEDURE SP_Integraciones_AsignarLotesLineas
    @Cantidad numeric, @producto nvarchar(max), @bodega nvarchar(max)

AS
BEGIN
    BEGIN TRY
    BEGIN TRAN

    DECLARE @temp as table(idLoteT nvarchar(max), CantidadT numeric, FechaT date) 
    DECLARE @loteAc nvarchar(Max), @CantidadAc numeric, @restante numeric , @Cont numeric, @fechaAc date, @resultado nvarchar(max)

    SET @Cont = @Cantidad
    DECLARE CursoIns CURSOR SCROLL 
    FOR SELECT IdLote, CONVERT(NUMERIC,cantidad), Fecha
                        FROM   tblintegraciones_lotes
                        WHERE idproducto = @producto 
                        AND Bodega = @bodega
                        AND cantidad > '0.0'
                        ORDER  BY fecha ASC

    OPEN CursoIns
    FETCH CursoIns INTO  @loteAc, @CantidadAc, @fechaAc

    WHILE( @Cont > 0  AND @@FETCH_STATUS = 0  )
      BEGIN

          IF( @CantidadAc >= @Cont )
              BEGIN
              SET @restante = @CantidadAc- @Cont 
                  INSERT INTO @temp(
                  idLoteT, CantidadT, FechaT
                  )VALUES(
                   @loteAc, @Cont, @fechaAc
                  )
                  UPDATE tblintegraciones_lotes SET Cantidad = @restante WHERE idProducto = @producto AND IdLote = @loteAc
                  SET @Cont = @cont - @CantidadAc                 
              END
              
            ELSE
              BEGIN
              SET @Cont = @Cont - @CantidadAc
              SET @restante =  @Cont - @CantidadAc    
                     INSERT INTO @temp(
                    idLoteT, CantidadT, FechaT
                    )VALUES(
                     @loteAc, @CantidadAc, @fechaAc
                    )
                    UPDATE tblintegraciones_lotes SET Cantidad = 0 WHERE idProducto = @producto AND IdLote = @loteAc                
              END
              FETCH CursoIns INTO  @loteAc, @CantidadAc, @fechaAc
        END 

    CLOSE CursoIns;  
    DEALLOCATE CursoIns; 

    SELECT * FROM @temp

    COMMIT TRANSACTION
    END TRY
  BEGIN CATCH
    ROLLBACK
    DECLARE @DescripcionError AS nvarchar(max)
    SET @DescripcionError = ERROR_MESSAGE()
    RAISERROR (@DescripcionError, 18, 1, 'Inventario - lotes - Integraciones', 5)
  END CATCH
END
GO

当前业务需求为在SELECT查询中调用上述库存分配逻辑，将最终查询结果通过FOR JSON PATH解析为JSON格式输出，初步梳理了两种可能的实现方向：

• 在SELECT语句中直接执行该存储过程
• 创建自定义函数调用该存储过程

需要嵌入该批次分配逻辑的查询语句如下：

(SELECT CASE
                     WHEN MFAC.codigo IN ('ME-14009',
                                          'ME-14010',
                                          'ME-14011') THEN 'IMP-BOLSAP'
                     ELSE MFAC.codigo
                 END AS ItemCode,
                 REPLACE(MFAC.cantidad, ',', '.') AS Quantity,
                 CASE WHEN MFAC.codigo LIKE '%PV%' THEN MFAC.valor ELSE null END as Price,
                 REPLACE(MFAC.descuento, ',', '.') AS DiscountPercent,
                 CASE
                     WHEN MFAC.codbodega = 'PV-PER' THEN 'PV-DQU'
                     ELSE MFAC.codbodega
                 END AS WarehouseCode,
                 @OcrCode3 AS CostingCode3,
                 @OcrCode3 AS COGSCostingCode3
                    FOR JSON PATH
                  ) DocumentLines

你最初设想的两个方案均不可行，原因如下：

1. SQL Server没有提供在SELECT语句中逐行直接调用存储过程的语法支持，无法在查询列定义里直接执行存储过程返回结果。
2. SQL Server对自定义函数有强约束：函数内禁止执行DML修改操作、禁止控制事务、禁止调用产生副作用的代码。你写的存储过程包含UPDATE语句、显式事务，完全不符合函数调用要求，网上流传的通过OPENROWSET、xp_cmdshell等方式绕过限制的方案性能极差、存在严重安全隐患，生产环境禁止使用。

最优实现方案：预计算分配结果后再拼接JSON #

不要强行把带数据修改的库存扣减逻辑塞进查询或函数里，遵循“先处理数据修改、再查询生成结果”的流程即可，具体步骤如下：

1. 落地待分配单据行到临时表
   先把需要做库存分配的所有单据行查询出来存入临时表，给每行加唯一标识方便后续关联，代码示例：

   SELECT 
       ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS LineId,
       CASE WHEN MFAC.codigo IN ('ME-14009','ME-14010','ME-14011') THEN 'IMP-BOLSAP' ELSE MFAC.codigo END AS ItemCode,
       CAST(REPLACE(MFAC.cantidad, ',', '.') AS numeric) AS Quantity,
       CASE WHEN MFAC.codigo LIKE '%PV%' THEN MFAC.valor ELSE NULL END AS Price,
       CAST(REPLACE(MFAC.descuento, ',', '.') AS numeric) AS DiscountPercent,
       CASE WHEN MFAC.codbodega = 'PV-PER' THEN 'PV-DQU' ELSE MFAC.codbodega END AS WarehouseCode,
       @OcrCode3 AS CostingCode3,
       @OcrCode3 AS COGSCostingCode3
   INTO #PendingAllocLines
   -- 此处替换为你原来查询MFAC数据的FROM/JOIN逻辑
   FROM 你的MFAC数据源表
   

2. 创建临时表存储分配结果
   建表结构匹配存储过程返回值，额外加LineId字段关联对应单据行：

   CREATE TABLE #AllocResult (
       LineId INT,
       idLoteT NVARCHAR(MAX),
       CantidadT NUMERIC,
       FechaT DATE
   )
   

3. 逐行调用存储过程完成库存扣减
   用游标遍历待分配的单据行，调用已写好的存储过程，把每一行的批次分配结果存入临时表：

   DECLARE @LineId INT, @ItemCode NVARCHAR(MAX), @Qty NUMERIC, @WhCode NVARCHAR(MAX)
   DECLARE cur CURSOR FAST_FORWARD FOR SELECT LineId, ItemCode, Quantity, WarehouseCode FROM #PendingAllocLines
   OPEN cur
   FETCH NEXT FROM cur INTO @LineId, @ItemCode, @Qty, @WhCode
   WHILE @@FETCH_STATUS = 0
   BEGIN
       INSERT INTO #AllocResult(LineId, idLoteT, CantidadT, FechaT)
       EXEC SP_Integraciones_AsignarLotesLineas @Cantidad = @Qty, @producto = @ItemCode, @bodega = @WhCode
       FETCH NEXT FROM cur INTO @LineId, @ItemCode, @Qty, @WhCode
   END
   CLOSE cur
   DEALLOCATE cur
   

   注意：你原有存储过程存在逻辑bug：当批次库存大于等于待扣数量时，执行SET @Cont = @cont - @CantidadAc会将@Cont计算为负数，导致循环异常，此处应改为SET @Cont = 0；另外库存不足分支里的@restante变量无实际作用，可以删除。
4. 关联结果生成最终JSON
   所有库存扣减完成后，直接关联临时表生成需要的JSON格式即可，如果需要把批次分配明细嵌入到对应单据行中，加子查询关联即可：

   SELECT 
       pl.ItemCode,
       pl.Quantity,
       pl.Price,
       pl.DiscountPercent,
       pl.WarehouseCode,
       pl.CostingCode3,
       pl.COGSCostingCode3,
       -- 如需嵌入批次明细，保留该子查询，不需要可删除
       (SELECT idLoteT AS BatchNo, CantidadT AS AllocatedQty, FechaT AS InboundDate FROM #AllocResult ar WHERE ar.LineId = pl.LineId FOR JSON PATH) AS AllocatedBatchs
   FROM #PendingAllocLines pl
   FOR JSON PATH
   

内容的提问来源于stack exchange，提问作者FoundingCoding99