SQL CASE语句是否以不同于列的方式处理变量？

I have the following code in a stored procedure and am trying to conditionally format a calculated number based on its length (if the number is less than 4 digits, pad with leading zeros). However, my case statement is not working. The "formattedNumber2" result is the one I'm looking for.

我在存储过程中有以下代码,并尝试根据其长度有条件地格式化计算的数字(如果数字小于4位,则使用前导零填充)。但是,我的案例陈述不起作用。 “formattedNumber2”结果是我正在寻找的结果。

I'm assuming the case statement treats the variable strangely, but I also don't know of a way around this.

我假设case语句奇怪地处理变量,但我也不知道解决这个问题的方法。

DECLARE @Number int = 5

SELECT
    CASE 
       WHEN (LEN(CONVERT(VARCHAR, @Number)) > 4) 
          THEN @Number
       ELSE RIGHT('0000' + CAST(@Number AS VARCHAR(4)), 4) 
    END AS formattedNumber,
    LEN(CONVERT(VARCHAR, @Number)) AS numberLength,
    RIGHT('0000' + CAST(@Number AS VARCHAR(4)), 4) AS formattedNumber2

I get the following results when I run the query:

运行查询时,我得到以下结果:

formattedNumber   numberLength  formattedNumber2
-------------------------------------------------
         5             1              0005

2 个解决方案

#1

SQL DEMO

The problem is you are using different data type on your case , integer and string. So the CASE stay with the first type he find and convert the rest.

问题是您在案例中使用不同的数据类型,整数和字符串。因此,CASE保留了他找到的第一种类型并转换其余类型。

 CASE WHEN (LEN(convert(VARCHAR, @Number)) > 4) THEN convert(VARCHAR, @Number)

#2

This can be done a lot easier with format() since version 2012.

从2012版开始,这可以通过format()轻松完成。

format(n,
       '0000')

And that would also handle negative values, which your current approach apparently doesn't.

这也会处理负值,而你现在的方法显然没有。

Prior 2012 it can be handled with basically replicate() and + (string concatenation).

在2012年之前,它可以使用基本上replicate()和+(字符串连接)来处理。

isnull(replicate('-',
                 -sign(n)), '')
+
isnull(replicate('0',
                 4
                 -
                 len(cast(abs(n) AS varchar(10)))
                 ),
       '')
+
cast(abs(n) AS varchar(10))

(It targets integer values, choose a larger length for the varchar casts for bigint.)

(它以整数值为目标,为bigint的varchar强制转换选择更大的长度。)

db<>fiddle

#1