How to return a table in SQL Server function

Question

How would I return a table from a SQL Server function?

In Postgres, I would simply do something like the following:

CREATE FUNCTION table_get(_active_bool BOOLEAN)
RETURNS TABLE(column integer)
language plpgsql
as $$
BEGIN
         RETURN QUERY
SELECT column
FROM table
WHERE active = _active_bool
END;
$$;

And it will just work.

For what ever reason I can't get this one to work in SQL Server.

CREATE FUNCTION hr.naughty_emp_id_get
    (@pquarter NVARCHAR(1),
     @pyear NVARCHAR(4))
RETURNS TABLE (employeeid INT)
AS
BEGIN
    WITH vars AS 
    (
         SELECT @pquarter AS pquarter, @pyear AS pyear
    )
    SELECT tblhr_employees.employeeid
    FROM hr.tblhr_employees
    INNER JOIN hr.tblHR_AttendancePunchTime ON tblhr_employees.employeeid       = tblHR_AttendancePunchTime.EmployeeID
    INNER JOIN hr.tblHR_AttendanceTimeCode  ON tblHR_AttendancePunchTime.CodeID = tblHR_AttendanceTimeCode.CodeID
    WHERE 1 = 1
      AND (tblHR_AttendanceTimeCode.CategoryID = 3
           OR tblHR_AttendanceTimeCode.CategoryID = 11)
      AND dbo.to_year_quarter(tblHR_AttendancePunchTime.AdjTimeIn) = (SELECT vars.pyear FROM vars) + '-' + (SELECT vars.pquarter FROM vars)
      AND tblhr_employees.separationdate IS NULL
    GROUP BY 
        tblhr_employees.employeeid;

    RETURN
END
GO

It is throwing this error:

Msg 156, Level 15, State 1, Procedure naughty_emp_id_get, Line 18 [Batch Start Line 6]
Incorrect syntax near the keyword 'BEGIN'

I tried adding ;s in various spots and it didn't seem to work

EzLo · Accepted Answer · 2018-06-19 13:45:14Z

3

You have mixed 2 ways of declaring the resulting temporal table.

Either declare as table variable and explicitly insert into it:

CREATE FUNCTION hr.naughty_emp_id_get
(
    @pquarter NVARCHAR(1)
  , @pyear NVARCHAR(4)
)
RETURNS @result TABLE (employeeid INT) -- Here
AS
BEGIN
    ;WITH vars AS (SELECT @pquarter AS pquarter, @pyear AS pyear)
    INSERT INTO @result (employeeid) -- And here
    SELECT tblhr_employees.employeeid
    FROM --...

END

Or avoid it's declaration altogether:

CREATE FUNCTION hr.naughty_emp_id_get
(
    -- Add the parameters for the function here
    @pquarter NVARCHAR(1)
  , @pyear NVARCHAR(4)
)
RETURNS TABLE AS RETURN
    WITH vars AS (SELECT @pquarter AS pquarter, @pyear AS pyear)
    SELECT tblhr_employees.employeeid
    FROM --...

answered Jun 19, 2018 at 13:45

EzLo

14.2k10 gold badges38 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jeroen Mostert Over a year ago

It's worth pointing out that this is not merely a syntax difference. The second version creates an inline table-valued function, which can be expanded by the optimizer when invoked in queries and typically has better performance characteristics than the non-inline variant (although occasionally the opposite can be the case, depending on how the function is used).

SQLApostle Over a year ago

Ideally what @JeroenMostert said is true, however Microsoft claims to have fixed this in SQL Server 2017 by adaptive query processing (youtube.com/watch?time_continue=2&v=szTmo6rTUjM). I don't have an instance to verify this , but I will try and get back on this topic when I can

Jeroen Mostert Over a year ago

@SQLApostle: SQL Server 2017 improves things by allowing accurate cardinality estimates for multi-statement TVFs and changing the execution plan based on that, which will improve their characteristics in many scenarios. However, the basic difference between multi-statement and inline TVFs remains: inline TVFs are expanded in the query, multi-statement TVFs are materialized first. There's use cases for both; the improvements for MSTVFs mean they can now be better than inline TVFs in more cases (especially for complex queries), making it even more important to try both.

Jeroen Mostert Over a year ago

Link to the docs with details for people who can't or don't want to watch videos.

SQLApostle · Accepted Answer · 2018-06-19 13:41:57Z

2

You are missing the table name for the table to be returned. This should work

 CREATE FUNCTION hr.naughty_emp_id_get
 (
     -- Add the parameters for the function here
     @pquarter NVARCHAR(1)
   , @pyear NVARCHAR(4)
 )
 RETURNS @employees TABLE (employeeid INT)
 AS
 BEGIN
 WITH vars AS (SELECT @pquarter AS pquarter, @pyear AS pyear)
 INSERT @employees
 SELECT tblhr_employees.employeeid
 FROM hr.tblhr_employees
 INNER JOIN hr.tblHR_AttendancePunchTime ON tblhr_employees.employeeid       = tblHR_AttendancePunchTime.EmployeeID
 INNER JOIN hr.tblHR_AttendanceTimeCode  ON tblHR_AttendancePunchTime.CodeID = tblHR_AttendanceTimeCode.CodeID
 WHERE 1=1
 AND (tblHR_AttendanceTimeCode.CategoryID      = 3
 OR tblHR_AttendanceTimeCode.CategoryID       = 11)
 AND dbo.to_year_quarter(tblHR_AttendancePunchTime.AdjTimeIn) = (SELECT vars.pyear FROM vars) + '-' + (SELECT vars.pquarter FROM vars)
 AND tblhr_employees.separationdate IS NULL
 GROUP BY tblhr_employees.employeeid;

     RETURN
 END

answered Jun 19, 2018 at 13:41

SQLApostle

5903 silver badges15 bronze badges

2 Comments

Daniel L. VanDenBosch Over a year ago

It did thank you. It ran and I was able to successfully use the following select. SELECT * FROM hr.naughty_emp_id_get('1','2018');

SMor Over a year ago

@DanielL.VanDenBosch If this works (and read the other suggestion - it is very important), then please accept it as the answer. That is how SO works, questions get asked and answered so others can find those answers.

Ross Bush · Accepted Answer · 2018-06-19 13:47:01Z

1

You have to insert into the resulting table variable.

RETURNS @MyTable TABLE (MyID INT)
AS BEGIN    
    INSERT INTO @MyTable SELECT 1
    RETURN 
END

edited Jun 19, 2018 at 13:47

answered Jun 19, 2018 at 13:43

Ross Bush

15.2k2 gold badges39 silver badges65 bronze badges

1 Comment

S3S Over a year ago

I think you have a syntax error here... missing the TABLE after the variable.

Collectives™ on Stack Overflow

How to return a table in SQL Server function

3 Answers 3

4 Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related