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.

It would really help if you formatted your code so it was readable! — Pieter Geerkens, Oct 16 '14 at 02:29
What do you want this function to return? The scalar returns an int, but the table logic looks to be trying to return a table variable containing a single row with a single column which contains an int? This doesnt seem to make a lot of sense: if you want this, why not use the scalar, and if you need to change it then this implies you need to get more back. — John Bingham, Oct 16 '14 at 04:08
I could not use scalar in EF 6.1; I thought maybe if I use TVF would work! — hncl, Oct 16 '14 at 04:38

score 0 · Accepted Answer · answered Oct 16 '14 at 02:29

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

Thank you, I now get Invalid column name 'ClinicId'. – hncl Oct 16 '14 at 02:31 — hncl, Oct 16 '14 at 02:31

Converting Scalar-Valued Function to Table-Valued Function

1 Answers1