SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


With Clause in SQL Server Function (UDF)


With Clause in SQL Server Function (UDF)

Author
Message
az1862
az1862
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 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)
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
weitzera
weitzera
Ten Centuries
Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)

Group: General Forum Members
Points: 1000 Visits: 629
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.'

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search