October 29, 2013 at 2:41 pm
I am trying to Write a function the needs with clause with in the function so
Create FUNCTION Temp(@Begin_Date DATE, @END_Date DATE)
RETURNS TABLE
AS
RETURN
(
---Also I need to declare var1 and var2 that will read data from a table for parameters
WITH Emp AS (
Select * FROM emp where hireDate BETWEEN @Begin_Date AND @END_Date
),
dep AS
(Select * from dept where deptStartDate BETWEEN @Begin_Date AND @END_Date)
---Then I need to return
Select * from dept, emp
where emp.empno = dept.empno;
)
So below are the questions I am unclear about
1. How to declare var1 and var2 that will read data from a table for parameters
2. How return all resultset.
The key is to be able to filter records before join.
Can we do it just in a view......?
Any help is appreciated
October 31, 2013 at 3:03 pm
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))
RETURNS TABLE
AS
RETURN
(
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
),
dept AS
(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
);
go
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)
)
select f.*
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.'
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply