Question

new to SQL programming, I have this function which works well:

(
@clientId AS int
)
RETURNS NVARCHAR(50)
AS
BEGIN
declare @result nvarchar(50);
declare @lastDate Date;
declare @dateBefore Date;
declare @count float;
set @count = (select  MAX(A1c)-MIN(A1c) as change from
(select A1c from 
( select top 2 * from vw_MasterView where clientId = @clientId order by vw_masterView.LastVisitDate desc  ) as vw
group by A1c) as hh);
RETURN @count
end;

由于我将重复此函数,以计算主视图中的其他列;我想将A1c改为变量,具体如下:

(
@clientId AS int,
@lab AS nvarchar(50)
)
RETURNS NVARCHAR(50)
AS
BEGIN
declare @result nvarchar(50);
declare @lastDate Date;
declare @dateBefore Date;
declare @count float;
set @count = (select  MAX(@lab)-MIN(@lab) as change from
(select @lab from 
( select top 2 * from vw_MasterView where clientId = @clientId order by vw_masterView.LastVisitDate desc  ) as vw
group by @lab) as hh);
RETURN @count
end;

我有以下错误:

Msg 164, Level 15, State 1, Procedure fn_changeA1c, Line 22
Each GROUP BY expression must contain at least one column that is not an outer reference.
Msg 8155, Level 16, State 2, Procedure fn_changeA1c, Line 22
No column name was specified for column 1 of  hh .

提前感谢。

Answer 1

您需要使用存储程序而不是函数。

create procedure myprocedure
    @clientId AS int,
    @lab AS nvarchar(50)
    AS
BEGIN
    declare @result nvarchar(50);
    declare @lastDate Date;
    declare @dateBefore Date;
    declare @count float;
    declare @sql nvarchar(500);
    set @sql =  select  MAX(  + @lab +  )-MIN(  + @lab +  ) as change from
    (select   + @lab +   from 
    ( select top 2 * from vw_MasterView where clientId =   + convert(nvarchar, @clientId) +   order by    vw_masterView.LastVisitDate desc  ) as vw
    group by   + @lab +  ) as hh ;
    execute sp_executesql @sql;
end;

友情链接