DECLARE a variable inside function in SQL Server?

For sql server declare table variable, 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. Note: Variables in SQL procedures are defined by using the DECLARE statement. Values can be assigned to variables using the SET statement or the SELECT INTO statement or as a default value when the variable is declared. Literals, expressions, the result of a query, and special register values can be assigned to variables.