Multiple errors in SQL Server Function

Question

I'm trying to make a function that evaluates the expiration date in a policy and based on that when is called in a Query it returns the text values ('Monthly'..and so on). but i'm getting a few errors of type "Incorrect syntax near Begin, declare expecting (.", "Must declare the scalar variable @policybillid" and in the end of the function "Incorrect syntax near ')'.". Any help would be appreciated.

USE [defaultDB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER FUNCTION [dbo].[fn_Payment_GetPaymentCoutesByPaymentID]( @policybillid BIGINT, @companyID BIGINT) Returns TABLE
AS
RETURN (
BEGIN( ***Incorrect syntax near 'BEGIN'.  Expecting '(', SELECT, or WITH.***
    declare @expiresDates as table(expiredOn date) ***Incorrect syntax near 'declare'.***  Expecting '(', or SELECT.

insert into @expiresDates
select top(2) expiredOn from PolicyPaymentPlan 
left join PolicyPaymentPlanFee on PolicyPaymentPlan.paymentPlanID= PolicyPaymentPlanFee.paymentPlanID
where PolicyPaymentPlan.policyBillID = @policybillid --Must declare the scalar variable "@policybillid".
order by cuoteID

select * from @expiresDates
declare @diffMonth int
declare @quota varchar(max) = ''

if((select count(*) from @expiresDates) > 1)
BEGIN
      set @diffMonth = DATEDIFF(month, (select top(1) expiredOn from @expiresDates),  (select top(1) expiredOn from @expiresDates order by expiredOn desc));       
      set @quota = ( SELECT 
      CASE 
            WHEN @diffMonth =1 
            then 
                   'Monthly'
            WHEN @diffMonth =2 THEN 
                  'bimonthly'
            WHEN @diffMonth =3 THEN 
                  'trimonthly '
            WHEN @diffMonth =4 THEN
                   'Four-Monthly'
            WHEN @diffMonth =6 THEN
                   'biannual'
            ELSE 'Cash' 
            END      
      )     
END
) ***Incorrect syntax near ')'.***

Multi-line Table Value functions perform *awfully*. If you want to use a TVF, make sure is an inline TVF. — Thom A, Jun 09 '22 at 17:39
Your errors, however, are due to your overuse of parentheses (`()`) and the `RETURN` trying to return a batch, not an inline statement or table variable. SQL isn't a programming language, and should not be written like one. — Thom A, Jun 09 '22 at 17:40
The syntax you want is `CREATE FUNCTION {Name} ({Parameters}) RETURNS table AS RETURN {Single SQL statement}`. Where you replace the parts in braces appropriately. Note that there is only *one* set of parenthesis, which wraps the parameters. — Thom A, Jun 09 '22 at 17:42

score 0 · Answer 1 · answered Jun 09 '22 at 21:01

There are numerous issues and syntax errors on your existing code. The main one is that an inline Table Valued Function can only have a single SELECT statement and nothing else.

So here is what it should look like.

CREATE OR ALTER FUNCTION [dbo].[fn_Payment_GetPaymentCoutesByPaymentID](
    @policybillid BIGINT,
    @companyID BIGINT
)
RETURNS TABLE
AS RETURN

SELECT Quota =
  CASE WHEN COUNT(*) > 1 THEN
      CASE DATEDIFF(month, MIN(expiredOn), MAX(expiredOn))
        WHEN 1 THEN 'Monthly'
        WHEN 2 THEN 'bimonthly'
        WHEN 3 THEN 'trimonthly '
        WHEN 4 THEN 'Four-Monthly'
        WHEN 6 THEN 'biannual'
        ELSE 'Cash' 
      END      
  END
FROM (
    select top (2) expiredOn
    from PolicyPaymentPlan pp
    join PolicyPaymentPlanFee ppf on pp.paymentPlanID = ppf.paymentPlanID
    where pp.policyBillID = @policybillid
    order by cuoteID
) pp
;

Multiple errors in SQL Server Function

1 Answers1