sql - 简化 If 和 else IF 查询

Question

ALTER PROCEDURE spDisplayTableNames1
    @T_ID INT OUT,
    @BatchNumber VARCHAR(30) OUT
AS
BEGIN
    IF((SELECT COUNT(*) FROM tblPacks 
        WHERE T_ID = @T_ID 
          AND BatchNumber = @BatchNumber) = 0) 
    AND ((SELECT COUNT(*) FROM tblBlisters 
          WHERE T_ID = @T_ID 
            AND BatchNumber = @BatchNumber) = 0) 
    AND ((SELECT COUNT(*) FROM tblShippers 
          WHERE T_ID = @T_ID AND BatchNumber = @BatchNumber) = 0)
BEGIN 
    PRINT '0 0 0'
END
ELSE IF((SELECT COUNT(*) FROM tblPacks WHERE T_ID=@T_ID AND BatchNumber=@BatchNumber)>0) AND ((SELECT COUNT(*) FROM tblBlisters WHERE T_ID=@T_ID AND BatchNumber=@BatchNumber)=0) AND ((SELECT COUNT(*) FROM tblShippers WHERE T_ID=@T_ID AND BatchNumber=@BatchNumber)=0)
BEGIN
Print '1 0 0'
END
ELSE IF((SELECT COUNT(*) FROM tblPacks WHERE T_ID=@T_ID AND BatchNumber=@BatchNumber)=0) AND ((SELECT COUNT(*) FROM tblBlisters WHERE T_ID=@T_ID AND BatchNumber=@BatchNumber)>0) AND ((SELECT COUNT(*) FROM tblShippers WHERE T_ID=@T_ID AND BatchNumber=@BatchNumber)=0)
BEGIN
Print '0 1 0'
END
ELSE IF((SELECT COUNT(*) FROM tblPacks WHERE T_ID=@T_ID AND BatchNumber=@BatchNumber)=0) AND ((SELECT COUNT(*) FROM tblBlisters WHERE T_ID=@T_ID AND BatchNumber=@BatchNumber)=0) AND ((SELECT COUNT(*) FROM tblShippers WHERE T_ID=@T_ID AND BatchNumber=@BatchNumber)>0)
BEGIN
Print '0 0 1'
END
ELSE IF((SELECT COUNT(*) FROM tblPacks WHERE T_ID=@T_ID AND BatchNumber=@BatchNumber)>0) AND ((SELECT COUNT(*) FROM tblBlisters WHERE T_ID=@T_ID AND BatchNumber=@BatchNumber)>0) AND ((SELECT COUNT(*) FROM tblShippers WHERE T_ID=@T_ID AND BatchNumber=@BatchNumber)=0)
BEGIN
Print '1 1 0'
END
ELSE IF((SELECT COUNT(*) FROM tblPacks WHERE T_ID=@T_ID AND BatchNumber=@BatchNumber)>0) AND ((SELECT COUNT(*) FROM tblBlisters WHERE T_ID=@T_ID AND BatchNumber=@BatchNumber)=0) AND ((SELECT COUNT(*) FROM tblShippers WHERE T_ID=@T_ID AND BatchNumber=@BatchNumber)>0)
BEGIN
Print '1 0 1'
END
ELSE IF((SELECT COUNT(*) FROM tblPacks WHERE T_ID=@T_ID AND BatchNumber=@BatchNumber)=0) AND ((SELECT COUNT(*) FROM tblBlisters WHERE T_ID=@T_ID AND BatchNumber=@BatchNumber)>0) AND ((SELECT COUNT(*) FROM tblShippers WHERE T_ID=@T_ID AND BatchNumber=@BatchNumber)>0)
BEGIN
Print '0 1 1'
END
ELSE IF((SELECT COUNT(*) FROM tblPacks WHERE T_ID=@T_ID AND BatchNumber=@BatchNumber)>0) AND ((SELECT COUNT(*) FROM tblBlisters WHERE T_ID=@T_ID AND BatchNumber=@BatchNumber)>0) AND ((SELECT COUNT(*) FROM tblShippers WHERE T_ID=@T_ID AND BatchNumber=@BatchNumber)>0)
BEGIN
Print '1 1 1'
END

END

这里我正在检查三个表的条件，如果一个列存在于三个表中，它应该打印“1 1 1”，如果它只存在于第一个和第三个表中，则打印“1 0 1”。就这样我写了 8 个条件语句。但是我想要一个简化查询上面的存储过程

我想要上面示例的最简单查询 - 我该怎么做？

Answer 1

试试这个:

declare @prt varchar(10);
if ((SELECT COUNT(*) FROM tblPacks WHERE T_ID=@T_ID AND BatchNumber=@BatchNumber)=0)
    set @prt = '0';
else
    set @prt = '1';

if ((SELECT COUNT(*) FROM tblBlisters WHERE T_ID=@T_ID AND BatchNumber=@BatchNumber)=0)
    set @prt = @prt + ' 0';
else
    set @prt = @prt + ' 1';

if ((SELECT COUNT(*) FROM tblShippers WHERE T_ID=@T_ID AND BatchNumber=@BatchNumber)=0) 
    set @prt = @prt + ' 0';
else
    set @prt = @prt + ' 1'; 

print @prt;