declare table variable inside function

Question

In my stored procedure, I have table variable with a lot of columns. Because I want to simplify the stored procedure I thought i could declare table variable in function like

CREATE FUNCTION [dbo].[ufn_DeclareMaterialTableVariable]
(
)
RETURNS @returntable TABLE
(
 [Id] [int] NULL,
 [Number] [int] NOT NULL,
 [Name] [nvarchar](255) NULL,
 ... dozens of columns
)
AS
BEGIN
 RETURN
END

but i realized i don't know, how to use it. My idea was something like

select * 
into @table
from [dbo].[ufn_DeclareMaterialTableVariable]

but this is not gonna work, unless i use temporary table.

The second idea is to declare table with custom data type.

declare @table TABLE as CustomTableType

but this is also not working. Any idea ?

Answer 1

You need to declare a table variable with the same shape as the TVF result, then use INSERT INTO not SELECT INTO, and remember to add brackets/parentheses to the end of the function name when invoking it, i.e. Foo() not just Foo:

DECLARE @Table TABLE (
 [Id] [int] NULL,
 [Number] [int] NOT NULL,
 [Name] [nvarchar](255) NULL,
 ... dozens of columns
);
// not best practice, but...
INSERT INTO @Table
SELECT *
FROM dbo.ufn_DeclareMaterialTableVariable();
// better - be explicit about the columns of interest
INSERT INTO @Table (
 [Id],
 [Number],
 [Name],
 ... dozens of columns
)
SELECT
 [Id],
 [Number],
 [Name],
 ... dozens of columns
FROM dbo.ufn_DeclareMaterialTableVariable();

If you've declared a table type, then the syntax for declaring a variable of that type is the following (note no TABLE keyword);

DECLARE @Table CustomTableType;

That said, annoyingly, you can't declare the result type of a TVF to be a table type - i.e. you always have to declare the returned columns again, explicitly - which partially negates the point of the custom type in the first place, in this situation.

• Muflix: Thank you, the second approach is exactly what i need :)