SQL Server 2012 CREATE FUNCTION编译错误

SQL Server 2012, Windows 2008 R2 Server

SQL Server 2012，Windows 2008 R2 Server

i am trying to create a function that takes a date range as parameters. It returns a resultset by selecting from a table for the given date range.

我正在尝试创建一个以日期范围为参数的函数。它通过从表中选择给定的日期范围来返回结果集。

I getting this error: Msg 102, Level 15, State 31, Procedure emp_performance_fn, Line 23 Incorrect syntax near 'BEGIN'.

我收到此错误：消息102，级别15，状态31，过程emp_performance_fn，第23行'BEGIN'附近的语法不正确。

CREATE FUNCTION emp_performance_fn (@startDate varchar(10), @endDate varchar(10))
RETURNS TABLE as
begin

declare   @vStartDate date;
declare    @vEndDate date ;
set @vStartDate = cast(@startDate as date);
    set @vEndDate = cast(@endDate as date);

RETURN
select emp_name, bugs_closed, avg_days_taken, 
(case bugs_closed_bucket when 1 then 'Low' when 4 then 'High' else 'Medium' end)
bugs_closed_bucket2,
(case avg_days_taken_bucket when 1 then 'Low' when 4 then 'High' else 'Medium' 
end) avg_days_taken_bucket2
from (
select emp_name, bugs_closed, avg_days_taken, ntile(4) over (order by 
avg_days_taken) avg_days_taken_bucket,
ntile(4) over (order by bugs_closed) bugs_closed_bucket
from (
SELECT t1.emp_name emp_name, sum(t1.bugs_closed) bugs_closed, 
avg(t1.avg_days_taken) avg_days_taken
FROM emp_performance t1
WHERE month_end_date between @vStartDate and @vEndDate
group by t1.emp_name
) v1) v2;
end;

Any ideas as to the cause of this? The SQL is valid (i ran it after commenting the WHERE clause). If i change the return type to INT and return 1, it compiles OK.

关于这个原因的任何想法？ SQL是有效的（我在评论WHERE子句后运行它）。如果我将返回类型更改为INT并返回1，则编译正常。

---

Fixed

modified the code to have this:

修改了代码以使其具有：

RETURNS @rtnTable TABLE (
emp_name varchar(100), 
bugs_closed numeric, 
avg_days_taken numeric,
bugs_closed_bucket_name varchar(100), 
avg_days_taken_bucket_name varchar(100)
)     

and

和

insert into @rtnTable select ....

later in the function body. That worked. Thanks for all the responses.

后来在函数体中。那很有效。感谢所有的回复。

1 个解决方案

#1

If you want to declare inline table function, you cannot declare any variables. And please format your query (not only when you post it to SO, but in your system too, Somebody sometimes will try to understand that, so it better be readable). I don't like nested subqueries, by you at least could do

如果要声明内联表函数，则不能声明任何变量。请格式化您的查询（不仅在您将其发布到SO时，而且在您的系统中也是如此，有时人们会尝试理解它，因此最好是可读的）。我不喜欢嵌套的子查询，至少你可以做到

CREATE FUNCTION emp_performance_fn (@startDate varchar(10), @endDate varchar(10))
RETURNS TABLE as
RETURN
(
    select
        emp_name, bugs_closed, avg_days_taken, 
        case bugs_closed_bucket
            when 1 then 'Low'
            when 4 then 'High'
            else 'Medium'
        end as bugs_closed_bucket2,
        case avg_days_taken_bucket
            when 1 then 'Low'
            when 4 then 'High'
            else 'Medium' 
        end as avg_days_taken_bucket2
    from (
        select
            emp_name, bugs_closed, avg_days_taken,
            ntile(4) over (order by   avg_days_taken) avg_days_taken_bucket,
            ntile(4) over (order by bugs_closed) bugs_closed_bucket
        from (
            select
                t1.emp_name emp_name, sum(t1.bugs_closed) bugs_closed, 
                avg(t1.avg_days_taken) avg_days_taken
            from emp_performance t1
            where
                month_end_date between @cast(@startDate as date) and cast(@endDate as date)
            group by t1.emp_name
        ) as v1
    ) as v2
)