[sql-server] Declare variable in table valued function

How can I declare a variable in a table valued function? (like in my title)

This question is related to sql-server function user-defined-functions

The answer is


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

Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to function

$http.get(...).success is not a function Function to calculate R2 (R-squared) in R How to Call a Function inside a Render in React/Jsx How does Python return multiple values from a function? Default optional parameter in Swift function How to have multiple conditions for one if statement in python Uncaught TypeError: .indexOf is not a function Proper use of const for defining functions in JavaScript Run php function on button click includes() not working in all browsers

Examples related to user-defined-functions

Hive: Convert String to Integer Declare variable in table valued function Execute Stored Procedure from a Function How to call a MySQL stored procedure from within PHP code? Multi-statement Table Valued Function vs Inline Table Valued Function Cannot find either column "dbo" or the user-defined function or aggregate "dbo.Splitfn", or the name is ambiguous Pass table as parameter into sql server UDF How to strip all non-alphabetic characters from string in SQL Server? How to determine the number of days in a month in SQL Server? TSQL How do you output PRINT in a user defined function?