存储过程测试以返回记录集

Question

我开始编写此SP只是为了发现它不起作用。如果不需要，我不想两次ping服务器。我基本上需要樱桃挑选已经存在的托盘的部分(指定为参数(，如果它们不存在，只需选择顶部。问题是，我想将其作为记录集返回，因此我得到了下一个X零件。我需要使用托盘"预测"。

SET @test = (SELECT [SERIAL_NUMBER] FROM [ROBOTICS_OPTICS_MECHUAT].[dbo].[AOF_ORDER_OPTICS] 
    WHERE ([RACK] = @leftStack AND [TRAY] = @leftTray) 
    OR ([RACK] = @midStack AND [TRAY] = @midTray) 
    OR ([RACK] = @rightStack AND [TRAY] = @rightTray))
IF NULLIF(@test, '') IS NULL
    BEGIN
        SELECT TOP 6 [RACK], [TRAY], [POSITION] FROM [ROBOTICS_OPTICS_MECHUAT].[dbo].[AOF_ORDER_OPTICS] 
            WHERE ([RACK] = @leftStack AND [TRAY] = @leftTray) 
            OR ([RACK] = @midStack AND [TRAY] = @midTray) 
            OR ([RACK] = @rightStack AND [TRAY] = @rightTray)
    END
ELSE
    BEGIN
        SELECT TOP 6 [RACK], [TRAY], [POSITION] FROM [ROBOTICS_OPTICS_MECHUAT].[dbo].[AOF_ORDER_OPTICS]
    END

Answer 1

您需要使您的选择测试将值分配给您的变量，例如：

SELECT @test = [SERIAL_NUMBER] FROM [ROBOTICS_OPTICS_MECHUAT].[dbo].[AOF_ORDER_OPTICS] 
WHERE ([RACK] = @leftStack AND [TRAY] = @leftTray) 
    OR ([RACK] = @midStack AND [TRAY] = @midTray) 
    OR ([RACK] = @rightStack AND [TRAY] = @rightTray)