Is there a way to write a function in SQL, which will return a column that you can call in a select statement?

Question

For example, I want to be able to write function 'f()' that I can call as written below. (Note that @tbl is a user-defined memory table that I've previously created with columns Var1 and Var2, and that my function 'f' calls for a parameter of user-defined table type.)

SELECT t1.Var1, t1.Var2, f(@tbl) OVER (PARTITION BY t1.Var1)
From @tbl as t1

It seems like user defined functions will either return table values or scalar values. I need to be able to return a column though, because I want to apply the function over a given column by group.

Answer 1

Scalar-Valued Function will give you column

Answer 2

with Scalar Function

eg:

1 create the function

CREATE FUNCTION [dbo].[duplicate] (@number int)
returns double 
as
begin
 declare @duplicate double
 set @duplicate = @number * 2
 return @duplicate
end

/****/

2 use the function

We have a table "numbers" like:

id | val

1 | 2

2 | 5

3 | 3

then

select id, duplicate(val) as dup
from numbers

the result:

id | dup

1 | 4

2 | 10

3 | 6