133

How can I declare a variable in a table valued function?

user16217248
  • 3,119
  • 19
  • 19
  • 37
esquare
  • 3,947
  • 10
  • 34
  • 37

2 Answers2

253

There are two flavors of table valued functions. One that is just a select statement and one that can have more rows than just a select statement.

This can not have a variable:

create function Func() returns table
as
return
select 10 as ColName

You have to do like this instead:

create function Func()
returns @T table(ColName int)
as
begin
  declare @Var int
  set @Var = 10
  insert into @T(ColName) values (@Var)
  return
end
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
  • 48
    The first example is known as an "Inline Table-Valued Function" which has performance benefits compared to a Multi-statement Table-Valued Function, namely the database server can *recompose* the query with the ITVF *inlined* into the parent query, essentially becoming a parameterised `VIEW` whereas a MSTVF behaves more like an opaque stored-procedure (though with its own advantages compared to sprocs). Inline functions should be preferred over MSTVF. If you do need to calculate and store intermediate values (such as the result of a complex scalar function expression) then use a subquery. – Dai Sep 24 '16 at 01:31
  • 3
    It's probably also worth mentioning that if the outcome of whatever you are using to populate the variable you wish to set is in any way generalisable, then you could consider writing a separate function to generate it. This would allow you to use the ITVF described by @Dai above, with all the benefits thereof, while still inserting a dynamically generated value into your function. I just wrote a function with the help of the above solution (thank you @MikaelEriksson!) which passes on one of its parameters to a helper function to save me having to use the MSTVF form. – naughtilus Jul 21 '17 at 15:02
  • 1
    the biggest cost is inserting for my function and I don't know how to skip this cost without inserting to table variable and return result of select – uzay95 Jun 24 '19 at 08:08
  • @naughtilus it would be great to see an example of this. Have you considered providing another answer along with your suggestion? – Jacques Jul 22 '19 at 14:03
1

In SQL Server:

It's not a very nice solution, but if you have a valid reason for needing to use an inline TVF instead of a MSTVF and cannot pass the variable into the TVF as a parameter, but can obtain it using a SELECT statement, you can use a CTE to access the value as follows:

CREATE FUNCTION func()
RETURNS TABLE
AS 
RETURN
(
-- Create a CTE table containing just the required value
WITH cte_myVar AS
   (SELECT <statement to select variable>) col1

-- Use the value by selecting it from the CTE table
SELECT * FROM table1 t1
WHERE t1.col1 = (SELECT col1 FROM cte_myVar)

)
Violet
  • 311
  • 1
  • 4
  • 13