Table function with table variable

Transact-SQL
1

I have tried every variation of this function from different post, but cannot figure out the correct syntex for using a table variable with a table function.

Please do not ask why I am doing this. There is much more to the function, but this is the basics of what I need to work. I just need to get the basic syntax to compile.

CREATE FUNCTION [dbo].[GET_test] 
(
    @UserId as nvarchar(50)
)
RETURNS TABLE
AS
BEGIN 


    DECLARE @R TABLE
    (
	   PMProjNum	 nvarchar(50),
	   PMProjName	 nvarchar(50)
    )



insert into @R

	   SELECT tblProjectMaster.PMProjNum,
			tblProjectMaster.PMProjName
 	   FROM tblProjectMaster
	   WHERE 
		  (tblProjectMaster.PMBuyer IS NULL OR tblProjectMaster.PMBuyer = '')
		  AND tblProjectMaster.PMStatus <> 'H'
		  AND tblProjectMaster.PMStatus <> 'C';


    -- SELECT PMProjNum, PMProjName FROM @R
    RETURN (SELECT PMProjNum, PMProjName, SUM(xxx) FROM @R SOME JOINS AND GROUP BY PMProjNum, PMProjName )`
    
   

END
GO
2

You're trying to build a multi-statement tvf. checkout the template in SSMS:

CREATE FUNCTION <Table_Function_Name, sysname, FunctionName> 
(
	-- Add the parameters for the function here
	<@param1, sysname, @p1> <data_type_for_param1, , int>, 
	<@param2, sysname, @p2> <data_type_for_param2, , char>
)
RETURNS 
<@Table_Variable_Name, sysname, @Table_Var> TABLE 
(
	-- Add the column definitions for the TABLE variable here
	<Column_1, sysname, c1> <Data_Type_For_Column1, , int>, 
	<Column_2, sysname, c2> <Data_Type_For_Column2, , int>
)
AS
BEGIN
	-- Fill the table variable with the rows for your result set
	
	RETURN 
END
GO

The table variable definition is part of the RETURN clause

3

I know you can declare a table you want to return... that is not what I am asking

I need to know how to declare a table VARIABLE within a table function, and then use it within the table function and THEN return results off of the table variable.

4

Right, I get that. If you follow the template you'll be OK

In your code you said

RETURNS TABLE
AS
DECLARE @tablename ..

but in the template it is

RETURNS @tablename ....

Do you see the difference?

5

Nope, still does not work and isn't what i was looking for. I want to declare a different table variable than the one that is being returned!

Below, also does not work.
Msg 444, Level 16, State 2, Procedure GET_test, Line 30
Select statements included within a function cannot return data to a client.

CREATE FUNCTION [dbo].[GET_test] 
(
    @UserId as nvarchar(50)
)
RETURNS 
    @R TABLE
    (
	   PMProjNum	 nvarchar(50),
	   PMProjName	 nvarchar(50)
    )

AS
BEGIN 



insert into @R

	   SELECT tblProjectMaster.PMProjNum,
			tblProjectMaster.PMProjName
 	   FROM tblProjectMaster
	   WHERE 
		  (tblProjectMaster.PMBuyer IS NULL OR tblProjectMaster.PMBuyer = '')
		  AND tblProjectMaster.PMStatus <> 'H'
		  AND tblProjectMaster.PMStatus <> 'C'


     SELECT PMProjNum, PMProjName FROM @R

    RETURN 
END
GO
6

remove this statement

SELECT PMProjNum, PMProjName FROM @R

7

I am not sure how else to explain this, but this will not be removed.

This select statement will be joing against MANY other tables, with grouping, to get counts.

I will not be returning the declared table @R, as stated several times.

Would be something like.

SELECT colA, colb, SUM(aaa * bbb), ...
FROM @R
JOINS other tables
GROUP BY .....

8

Hi

Please take a look at this link

Hopefully this helps

9

You don't need the final select. What you do is populate your table defined in the return clause. When the function is called, the caller receives the contents of that table as a result set. What you do in the body of the function is up to you. You can assemble data from wherever, however you like, just populate the result table at the end. but don't end with a SELECT, it's redundant (and invalid syntax)