Converting Scalar-Valued Function to Table-Valued Function

Question

I am converting an existing function from Scalar to Table-Valued, here is the scalar function:

ALTER FUNCTION [dbo].[fn_TotalClient]
(
@ClinicId AS int,
@FromDate AS DateTime,
@ToDate AS DateTime
)
RETURNS int
AS
BEGIN
DECLARE @result  int;
set @result = (Select TOP 1  Count(*) as  TotalClient
FROM (SELECT        dbo.Clinic.ClinicId,  Count(*) as TotalVisit
FROM            dbo.Clinic INNER JOIN
                         dbo.Visit ON dbo.Clinic.ClinicId = dbo.Visit.ClinicId
                          Where dbo.Visit.Date >= @FromDate AND dbo.Visit.Date <= @toDate AND dbo.Visit.ClinicId = @ClinicId 
                         Group By dbo.Clinic.ClinicId, dbo.Visit.ClientId) as v
                         Group By v.ClinicId
                         );
RETURN @result;
END

Here is my attempt Table-Valued:

CREATE FUNCTION [dbo].[TotalClient]( 

@ClinicId AS int,
@FromDate AS DateTime,
@ToDate AS DateTime
)
RETURNS TABLE
AS
RETURN
Select TOP 1  Count(*) as  TotalClient
FROM (
SELECT        Count(*) as TotalVisit
FROM            dbo.Clinic INNER JOIN
                dbo.Visit ON dbo.Clinic.ClinicId = dbo.Visit.ClinicId
                WHERE dbo.Visit.Date >= @FromDate AND dbo.Visit.Date <= @toDate AND dbo.Visit.ClinicId = @ClinicId
                Group By dbo.Clinic.ClinicId, dbo.Visit.ClientId)  as v
                Group By v.ClinicId)
RETURNS int

I get this error:

Msg 102, Level 15, State 1, Procedure TotalClient, Line 24 Incorrect syntax near ')'.

I don't have experience in this area and would appreciate your suggestions.

Answer 1

Remove the last ) and also the last RETURNS int line. i.e. your new SQL should look like so.

CREATE FUNCTION [dbo].[TotalClient]( 
    @ClinicId AS int,
    @FromDate AS DateTime,
    @ToDate AS DateTime
) RETURNS TABLE
AS
RETURN
SELECT TOP 1 Count(*) as TotalClient
FROM ( SELECT Clinic.ClinicId, Count(*) as TotalVisit
        FROM  dbo.Clinic INNER JOIN
            dbo.Visit ON dbo.Clinic.ClinicId = dbo.Visit.ClinicId
        WHERE dbo.Visit.Date >= @FromDate AND dbo.Visit.Date <= @toDate 
            AND dbo.Visit.ClinicId = @ClinicId
        GROUP BY dbo.Clinic.ClinicId, dbo.Visit.ClientId 
      ) AS v
GROUP BY v.ClinicId