Declare variable in table valued function

sql-server function user-defined-functions

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 tableasreturnselect 10 as ColName

You have to do like this instead:

create function Func()returns @T table(ColName int)asbegin  declare @Var int  set @Var = 10  insert into @T(ColName) values (@Var)  returnend

CodeHunter