Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

With Clause in SQL Server Function (UDF) Expand / Collapse
Posted Tuesday, October 29, 2013 2:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 29, 2013 6:25 PM
Points: 4, Visits: 10
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)



---Also I need to declare var1 and var2 that will read data from a table for parameters

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
Post #1509570
Posted Thursday, October 31, 2013 3:03 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, June 18, 2015 12:51 PM
Points: 884, Visits: 622
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
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


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.'
Post #1510430
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse