问题描述
我创建了一个 UDF 来计算数据库中的年龄段.我使用了以下代码
I created a UDF to calculate age bracket in the database. i used the following codes
CREATE FUNCTION Agebracket(@Ages INT) RETURNS VARCHAR AS BEGIN DECLARE @Age_Group varchar SET @Age_Group = CASE WHEN @Ages BETWEEN 0 AND 9 THEN '[0-9]' WHEN @Ages BETWEEN 10 AND 19 THEN '[10-19]' WHEN @Ages BETWEEN 20 AND 29 THEN '[20-29]' WHEN @Ages BETWEEN 30 AND 39 THEN '[30-39]' WHEN @Ages BETWEEN 40 AND 49 THEN '[40-49]' WHEN @Ages BETWEEN 50 AND 59 THEN '[50-59]' WHEN @Ages BETWEEN 60 AND 69 THEN '[60-69]' WHEN @Ages BETWEEN 70 AND 79 THEN '[70-79]' WHEN @Ages BETWEEN 80 AND 89 THEN '[80-89]' WHEN @Ages BETWEEN 90 AND 99 THEN '[90-99]' WHEN @Ages>=100 THEN '[100+]' end RETURN @Age_Group END
当我用下面的例子进行测试时:
when i test with the example below:
SELECT [dbo].[Agebracket](10)
输出为 [.
关于我可以做什么的任何想法,因为我希望输出为 [10-19]
Any idea on what i can do as i expect the output to be [10-19]
推荐答案
用 DECLARE @Age_Group varchar(8) 替换 DECLARE @Age_Group varchar 并使你的函数成为返回 varchar(8).
Replace DECLARE @Age_Group varchar with DECLARE @Age_Group varchar(8) and also make your function to return varchar(8).
工作版本:
alter FUNCTION Agebracket(@Ages INT) RETURNS VARCHAR(8) AS BEGIN DECLARE @Age_Group varchar(8) SET @Age_Group = CASE WHEN @Ages BETWEEN 0 AND 9 THEN '[0-9]' WHEN @Ages BETWEEN 10 AND 19 THEN '[10-19]' WHEN @Ages BETWEEN 20 AND 29 THEN '[20-29]' WHEN @Ages BETWEEN 30 AND 39 THEN '[30-39]' WHEN @Ages BETWEEN 40 AND 49 THEN '[40-49]' WHEN @Ages BETWEEN 50 AND 59 THEN '[50-59]' WHEN @Ages BETWEEN 60 AND 69 THEN '[60-69]' WHEN @Ages BETWEEN 70 AND 79 THEN '[70-79]' WHEN @Ages BETWEEN 80 AND 89 THEN '[80-89]' WHEN @Ages BETWEEN 90 AND 99 THEN '[90-99]' WHEN @Ages>=100 THEN '[100+]' end RETURN @Age_Group END GO SELECT [dbo].[Agebracket](10)
这是因为 SQL Server 假定 VARCHAR = VARCHAR(1),更糟糕的是,它会以静默方式截断值.
This is because SQL Server assumes VARCHAR = VARCHAR(1) and even worse, it will silently truncate the values.