0

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 ?

2

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 Aug 20 '19 at 11:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.