存储过程测试返回记录集

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)