我正在处理一些存储过程,特别是一个存储过程,它要求我对select的特定字段使用条件。我的select是这样的:
acc.AccountNum AS [Voucher_ID],
pol.Number AS [Policy_Number],
veh.Vehicle AS [VIN],
我要做的是动态地将列别名更改为AS,然后根据条件进行修改,我试图像这样插入一个if:
acc.AccountNum AS If(@x = 1){[Voucher_ID]} else {[Voucher_ID2]},
但它不起作用,我也尝试了一种情况,但也没有成功,有没有办法做到这一点,或者我必须创建两个不同的选择?
正如@Larnu在评论中提到的,您不能使用动态列字面量。您可以同时拥有别名和基于可空性的拾取。
CASE @x = 1 then acc.AccountNum ELSE NULL END AS [Voucher_ID],
CASE @x <> 1 OR @x IS NULL then acc.AccountNum ELSE NULL END AS [Voucher_ID2],
pol.Number AS [Policy_Number],
veh.Vehicle AS [VIN],
别名必须是文字。这意味着在我的应用程序中所要求的只有一种方式是可行的:创建两个不同的select,并在select之前插入所需的条件,如下所示:
IF @x = 1
SELECT acc.AccountNum AS [Voucher_ID],
pol.Number AS [Policy_Number],
veh.Vehicle AS [VIN]
FROM program WHERE ...
IF @x = 2
SELECT acc.AccountNum AS [Voucher_ID2],
pol.Number AS [Policy_Number],
veh.Vehicle AS [VIN]
FROM program WHERE ...