SQL Server 用户定义函数来计算年龄段

问题描述

我创建了一个 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.