Error when creating function SQL Server 2012

Question

I am creating a function so I can take the results of my select statement and place them in a view within our database. My SELECT statement returns one result when I run it, but when I go and place it in to create a function I get the error:

Only one expression can be specified in the select list when the 
subquery is not introduced with EXISTS.

I am still new and learning and would appreciate any help in what I am doing wrong.

CREATE FUNCTION CXi_Application 

@Application nvarchar(255), @Wave nvarchar(255)

RETURNS int
AS
BEGIN

DECLARE @Return int


SET @Return = 
(SELECT @Application, (
(sum(CASE WHEN overall_score IN (1) THEN 1 ELSE 0 END) + sum(CASE WHEN effective_score IN (1) THEN 1 ELSE 0 END) + sum(CASE WHEN easeuse_score IN (1) THEN 1 ELSE 0 END)) + 
(sum(CASE WHEN overall_score IN (2) THEN 2 ELSE 0 END) + sum(CASE WHEN effective_score IN (2) THEN 2 ELSE 0 END) + sum(CASE WHEN easeuse_score IN (2) THEN 2 ELSE 0 END)) + 
(sum(CASE WHEN overall_score IN (3) THEN 3 ELSE 0 END) + sum(CASE WHEN effective_score IN (3) THEN 3 ELSE 0 END) + sum(CASE WHEN easeuse_score IN (3) THEN 3 ELSE 0 END)) + 
(sum(CASE WHEN overall_score IN (4) THEN 4 ELSE 0 END) + sum(CASE WHEN effective_score IN (4) THEN 4 ELSE 0 END) + sum(CASE WHEN easeuse_score IN (4) THEN 4 ELSE 0 END)) + 
(sum(CASE WHEN overall_score IN (5) THEN 5 ELSE 0 END) + sum(CASE WHEN effective_score IN (5) THEN 5 ELSE 0 END) + sum(CASE WHEN easeuse_score IN (5) THEN 5 ELSE 0 END)))/
(count(overall_score) + count(effective_score) + count(easeuse_score) * 1.00 )
FROM FY14_DataMerge
WHERE [Status] = 'Completed' AND [Application] = @Application AND [Wave] = @Wave);

-- Return the result of the function
RETURN @Return

END
GO

The error is self explanatory. Run your `SELECT` statement and see how many columns it outputs. You can only have one. — Kermit, Dec 10 '13 at 22:30
Yes, I see that now. Thank you for helping me with the obvious. :) — Jaimiee Bayliss, Dec 12 '13 at 14:54

score 7 · Answer 1 · edited May 23 '17 at 10:33

You've specified in the DDL that you'd like the function to return an INT

RETURNS int

however, your result set includes @Application and the calculation itself. Essentially, you've declared that you are creating a Scalar-Valued Function but you're code is attempting to return a Table-Valued result. First you need to decide which you'd like to actually do.

If you're returning a single result (commonly found in the SELECT portion of the calling statement) then chances are you're looking for a Scalar-Valued function and you need to remove the @Application call from your SELECT statement in the function DDL.

If you actually would like the value returned from the function then you're going to need a Table-Valued Function. Additionally, this is a pretty straightforward query so you can make it an Inline TVF which has definite performance benefits, though I'm not sure how clearly they will be realized in your case.

Here is what it would look like to make an Inline TVF:

CREATE FUNCTION CXi_Application 

@Application nvarchar(255), @Wave nvarchar(255)

RETURNS TABLE
AS
RETURN SELECT @Application as [Application], (
(sum(CASE overall_score WHEN 1 THEN 1 ELSE 0 END) 
    + sum(CASE effective_score WHEN 1 THEN 1 ELSE 0 END) 
    + sum(CASE easeuse_score WHEN 1 THEN 1 ELSE 0 END)) 
    + 
(sum(CASE overall_score WHEN 2 THEN 2 ELSE 0 END) 
    + sum(CASE effective_score WHEN 2 THEN 2 ELSE 0 END) 
    + sum(CASE easeuse_score WHEN 2 THEN 2 ELSE 0 END)) 
    + 
(sum(CASE overall_score WHEN 3 THEN 3 ELSE 0 END) 
    + sum(CASE effective_score WHEN 3  THEN 3 ELSE 0 END) 
    + sum(CASE easeuse_score WHEN 3 THEN 3 ELSE 0 END)) 
    + 
(sum(CASE overall_score WHEN 4 THEN 4 ELSE 0 END) 
    + sum(CASE effective_score WHEN 4 THEN 4 ELSE 0 END) 
    + sum(CASE easeuse_score WHEN 4 THEN 4 ELSE 0 END)) 
    + 
(sum(CASE overall_score WHEN 5 THEN 5 ELSE 0 END) 
    + sum(CASE effective_score WHEN 5 THEN 5 ELSE 0 END) 
    + sum(CASE easeuse_score WHEN 5 THEN 5 ELSE 0 END))) 
/
(count(overall_score) + count(effective_score) 
    + count(easeuse_score) * 1.00 ) as CalcMetric
FROM FY14_DataMerge
WHERE [Status] = 'Completed' AND [Application] = @Application AND [Wave] = @Wave;
GO

However, if 5 is the max value for each data point, then why not refactor as:

CREATE FUNCTION CXi_Application 

@Application nvarchar(255), @Wave nvarchar(255)

RETURNS TABLE
AS
RETURN 
    SELECT @Application as [Application], 
        (SUM(overall_score) + SUM(effective_score) + SUM(easeuse_score))
          / 
        (count(overall_score) + count(effective_score) 
           + count(easeuse_score) * 1.00 )
        FROM FY14_DataMerge
        WHERE [Status] = 'Completed' 
           AND [Application] = @Application 
           AND [Wave] = @Wave;
GO

Shouldn't the `@Application` thing be aliased for the output, though? — Andriy M, Dec 11 '13 at 12:56
Thank you so much. I ended up removing the @Application, as I only was using that in the stored procedure for references, and it worked. I appreciate your help. — Jaimiee Bayliss, Dec 12 '13 at 14:53

Error when creating function SQL Server 2012

1 Answers1