SQL功能，通过参数

Question

选择特定的专栏中，我有一个表，看起来像

projectID , year , jan , feb , mar , apr , may .... 

每一个reprsent数字coulm ，我想在一个特定的colums是由价值决定的价值发现这个功能。

我有这个功能

CREATE FUNCTION [dbo].[func_] 
( @BRANCH_ID  NUMERIC(6,0) = 0, 
    @JOB_TYPE_ID NUMERIC(6,0) = 0, 
    @PROJECT_ID  NUMERIC(6,0) = 0, 
    @MONTH_NAME  varchar(10) = 0,  
    @YEAR_NUMBER NUMERIC(6,0) = 0) 
RETURNS NUMERIC(6,1) 
AS 
BEGIN 
    DECLARE @FORCAST NUMERIC(6,1) 

    SELECT @FORCAST = @MONTH_NAME 
    FROM TABLE 
    WHERE Projectid = @PROJECT_ID         
      AND Year = @YEAR_NUMBER 

    IF @FORCAST IS NULL 
     RETURN(0) 

    RETURN(@FORCAST) 
END 

例如，如果我执行功能和今天今天发送的年份和月份，将在“月”列返回值。

Answer 1

你可以使用UNPIVOT，如果它在你的SQL Server的版本（这可从SQL Server 2005开始）：

SELECT @FORCAST = Forecast 
FROM [TABLE] t 
UNPIVOT (
    Forecast FOR Month IN (jan, feb, mar, ...) 
) u 
WHERE Projectid = @PROJECT_ID 
    AND Year  = @YEAR_NUMBER 
    AND Month  = LEFT(@MONTH_NAME, 3) 

否则，它可能是这样的：

SELECT 
    @FORCAST = CASE @MONTH_NAME 
    WHEN 'January' THEN jan 
    WHEN 'February' THEN feb 
    WHEN 'March' THEN mar 
    ... 
    END 
FROM [TABLE] 
WHERE Projectid = @PROJECT_ID 
    AND Year  = @YEAR_NUMBER