how to call a table valued function with parameter

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to call a table valued function with parameter

Author

Topic

gagani
Posting Yak Master

112 Posts

Posted - 2012-07-18 : 11:47:31

A table valued function takes orderid as the input and returns orderid, cashvalue
and financevalue.

ALTER FUNCTION [dbo].[FN_CASHANDFINANCE]
(
@ORDERID as BIGINT
)
RETURNS @result TABLE
(
ORDERID BIGINT,
CASHVALUE MONEY,
FINANCEVALUE MONEY
)
AS
BEGIN
insert @result
SELECT orderid,CASHVALUE, FINANCEVALUE from audit_orders with(nolock) where orderid = @orderid and
trantimestamp in(select min(trantimestamp) from audit_orders with(nolock) where orderid =@orderid)
return
END

i need a query to call the table value function for every orderid of orders table.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-07-18 : 11:57:26

try this:


select cf.*
from   orders o
cross apply dbo.FN_CASHANDFINANCE(o.orderid) cf

Be One with the Optimizer
TG

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-07-18 : 12:00:59

A better, inline version would be:

ALTER FUNCTION [dbo].[FN_CASHANDFINANCE](@ORDERID AS BIGINT)
RETURNS TABLE AS RETURN(
SELECT orderid,CASHVALUE, FINANCEVALUE 
FROM audit_orders 
WHERE orderid = @orderid AND 
trantimestamp IN(SELECT MIN(trantimestamp) FROM audit_orders WITH(NOLOCK) WHERE orderid =@orderid))
GO

And if you only expect (or want) a single row to be returned:

ALTER FUNCTION [dbo].[FN_CASHANDFINANCE](@ORDERID AS BIGINT)
RETURNS TABLE AS RETURN(
SELECT TOP 1 orderid,CASHVALUE, FINANCEVALUE 
FROM audit_orders 
WHERE orderid = @orderid ORDER BY trantimestamp ASC)
GO

Unless you have specifically tested, do not include NOLOCK hints in your queries.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-07-18 : 12:07:00

inline functions are better when you CROSS apply or join to them as the code is substituted out and behaves more like VIEW.

The performance difference can sometimes be considerable.

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-07-18 : 13:12:36

Because you want to call this for all orderids you may want to consider a table valued function that doesn't take a specific orderid:


create function dbo.tvf_cashandfinance()
returns table
as
return
       select ao.orderid
              ,ao.CashValue
              ,ao.FinanceValue
       from   (
              select orderid
                     ,min(trantimestamp) trantimestamp
              from   audit_orders
              group by orderid
              ) d
       inner join audit_orders ao
              on ao.orderid = d.orderid
              and ao.trantimestamp = d.trantimestamp
go

--all orderids
select * from dbo.tvf_cashandfinance()

--specific set of orderids
select * from dbo.tvf_cashandfinance() where orderid in (1,2,3)

--single orderid
select * from dbo.tvf_cashandfinance() where orderid = 1

Be One with the Optimizer
TG

gagani
Posting Yak Master

112 Posts

Posted - 2012-07-18 : 15:37:03

i am using sql 2000, cross apply is working in it. It's there since sql 2005 i think. Is there any replacement for it. From the above post, I cannot really work on audit_orders table.
is there any replacement for the following in stead of cross apply
select cf.*
from orders o
cross apply dbo.FN_CASHANDFINANCE(o.orderid) cf

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-07-18 : 15:41:32

I believe the statement I posted (just above as the body of tvf_cashandfinance()) is equivalent to

select cf.*
from orders o
cross apply dbo.FN_CASHANDFINANCE(o.orderid) cf

Be One with the Optimizer
TG

gagani
Posting Yak Master

112 Posts

Posted - 2012-07-19 : 04:11:33

I apologise, I wanted to say that cross apply is not working in 2000.
so I want something which replaces cross apply in the above select query

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-07-19 : 04:38:23

JOIN to the code that TG posted.....

However, I think it would be better as a VIEW rather than a function if that's how you want to use it.

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources