sql-serverfunctionuser-defined-functionsdeclaretable-functions

Declare variable in table valued function

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

Solution

  • 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