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 ?

Chris Rolliston · Accepted Answer · 2019-08-20 09:54:41Z

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.

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

Muflix
– Muflix

2019-08-20 11:08:00 +00:00
Commented Aug 20, 2019 at 11:08 — Muflix
– Muflix, Commented Aug 20, 2019 at 11:08

Stack Exchange Network

declare table variable inside function

1 Answer 1

Your Answer

Hot Network Questions

declare table variable inside function

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions