Your example is what is called an inline table-valued function, and it works very similar to a view.
There is also a multi-statement table valued function that allows you to do most of what you could in a stored proc, such as declaring variables.
You can find the syntax for a multi-statement function here
, but the short answer to your question of how you return the values is that the syntax requires you to declare a table variable, so to return results you insert them into the result variable.
In most cases, you're better off using the syntax you outlined, however, because it allows the query processor to perform optimizations. Can't you add another couple of CTEs to calculate your var1 and var2 values, instead of sticking them into actual variables?
Another way you could avoid moving to the multi-statement TVF would be to add var1 and 2 as parameters, and pass them in like this:
alter FUNCTION Temp(@Begin_Date DATE, @END_Date DATE, @var1 varchar(30), @var2 varchar(30))
WITH tempEmp as (
Select * FROM (values (1, 'old man', '1/1/1900'),(2, 'young man', '12/31/2012'))emp(empno, empName, hireDate)
tempDept as (
Select * from (values (1, 'typewriters', '1/1/1910'),(2, 'smartphones', '12/31/2012'))dep(empno, department, deptStartDate)
Emp AS (
Select * FROM tempEmp where hireDate BETWEEN @Begin_Date AND @END_Date
(Select * from tempDept where deptStartDate BETWEEN @Begin_Date AND @END_Date)
Select empName, department, hireDate, deptStartDate from dept, emp
where emp.empno = dept.empno
and empName like @var1
and department like @var2
select * from temp('1/1/1900', '12/31/2012', '%man', '%');
with parameterTable as (
select * from (values ('Old Man', 'typewriters'), ('young man', 'smartphones') ) foo(var1, var2)
from parameterTable pt
cross apply temp('1/1/1900', '12/31/2012', pt.var1, pt.var2) f
(ignore tempEmp, tempDept, and parameterTable. I just included them because you didn't include sample data.)
Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'