我有这个我正在创建的功能.当我解析它,它工作正常,但实际上创建数据库中的功能,它说我的列名无效.这是不正确的,我拼写正确.这是代码：
ALTER FUNCTION [dbo].[fnTally] (@SchoolId nvarchar(50))
RETURNS int
AS
BEGIN
DECLARE @Final nvarchar
IF EXISTS (
SELECT
question,yes_ans,no_ans,na_ans,blank_ans
FROM dbo.qrc_maintally
WHERE school_id = @SchoolId
)
IF yes_ans > no_ans AND yes_ans > na_ans
BEGIN
SET @Final = 'Yes'
END
ELSE IF no_ans > yes_ans AND no_ans > na_ans
BEGIN
SET @Final = 'No'
END
ELSE IF na_ans > yes_ans AND na_ans > no_ans
BEGIN
SET @Final = 'N/A'
END
RETURN @Final
END
解决方法
您将需要为这些列创建局部变量,在选择期间分配它们,并将其用于条件测试.
declare @yes_ans int,@no_ans int,@na_ans int
SELECT @yes_ans = yes_ans,@no_ans = no_ans,@na_ans = na_ans
from dbo.qrc_maintally
where school_id = @SchoolId
If @yes_ans > @no_ans and @yes_ans > @na_ans
begin
Set @Final = 'Yes'
end
-- etc.
