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

Question

我开始写这个SP只是为了发现它不起作用。如果没有必要的话，我不想要两次打开服务器。我基本上需要选择托盘已经存在的部分(变量指定为参数)，如果它们不存在，只需选择顶部的部分。问题是，我想把它作为一个记录集返回，所以我得到了下一个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测试将值赋值给变量，如下所示：

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)