Passing parameter to table-valued function

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

SQL Server 2005 Forums

Transact-SQL (2005)

Passing parameter to table-valued function

Author

Topic

Buzzard724
Yak Posting Veteran

66 Posts

Posted - 2010-02-19 : 15:15:27

What I want to do is provide a stored date value to the parameter of a table-valued-function. The function is called from a view.

I can call this successfully as follows

SELECT PP.* FROM dbo.PayrollChangesAudit('2010-02-19 16:47:49.000') PP

Adding a further join to the query also works

SELECT P.FullName, PP.* FROM dbo.PayrollChangesAudit('2010-02-19 16:47:49.000') PP
INNER JOIN PEOPLE P ON P.People_ID = PP.PeopleID

However when I try to substitute a field as the parameter it fails with "The multi-part identifier P.DateToAudit cannot be bound"

SELECT P.FullName, PP.* FROM dbo.PayrollChangesAudit(P.DateToAudit) PP
INNER JOIN PEOPLE P ON P.People_ID = PP.PeopleID

I have used a similar structure to pass parameters to scalar-valued functions - how can I resolve this? thanks for your help

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-19 : 15:22:21

Possibly this?

SELECT P.FullName, PP.* FROM PEOPLE P 
CROSS APPLY dbo.PayrollChangesAudit(P.DateToAudit) PP

Buzzard724
Yak Posting Veteran

66 Posts

Posted - 2010-02-19 : 17:04:20

Thank you - I have slightly adapted your idea - as for practical purposes the DateToAudit field could return multiple values. So this now works with only one date value being returned. In order to use the ORDER BY I had to use TOP.

SELECT TOP 10 PP.* FROM Company C
CROSS APPLY dbo.PayrollChangesAudit(C.DateTest) PP
WHERE C.Company_ID = 'BF4800A3-5E15-419D-BA43-CFB0F4B6CE87'
ORDER BY PP.FullName, PP.RecordType DESC

I guess the original does not work becasue the parameter cannot be retrieved before the dataset in the table-valued-function and the function cannot return a record set without the parameter!!

Thank you very much for your help - problem solved
PS - for anyone else reading this article the CROSS APPLY is explained really well here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49306

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-20 : 00:08:17

quote:
Originally posted by Buzzard724

Thank you - I have slightly adapted your idea - as for practical purposes the DateToAudit field could return multiple values. So this now works with only one date value being returned. In order to use the ORDER BY I had to use TOP.

SELECT TOP 10 PP.* FROM Company C
CROSS APPLY dbo.PayrollChangesAudit(C.DateTest) PP
WHERE C.Company_ID = 'BF4800A3-5E15-419D-BA43-CFB0F4B6CE87'
ORDER BY PP.FullName, PP.RecordType DESC

I guess the original does not work becasue the parameter cannot be retrieved before the dataset in the table-valued-function and the function cannot return a record set without the parameter!!

Thank you very much for your help - problem solved
PS - for anyone else reading this article the CROSS APPLY is explained really well here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49306

see what all you can do with apply operator

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources