問題描述

我創建了一個 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.

這篇關于SQL Server 用戶定義函數來計算年齡段的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！