Using correlated parameter values with Table-valued functions

Question

Post reply

Using correlated parameter values with Table-valued functions

Mike Frederick

Newbie

Points: 9
More actions
August 8, 2007 at 7:50 pm

#118052

I have a table-valued function that I want to call using a value from another table in the SQL statement as the parameter value, like this:
select
p.*
from people p,
GetFirstAndLastGiftInfo
(p.people_guid)
where
people_guid = '565AC73F-51D9-4991-AB18-809A089F4A72'
I get the error "The multi-part identifier "p.people_guid" could not be bound."
But, the query will execute with a hard-coded value like below:
select
p.*
from
people p,
GetFirstAndLastGiftInfo
('565AC73F-51D9-4991-AB18-809A089F4A72')
where
people_guid = '565AC73F-51D9-4991-AB18-809A089F4A72'
Does anyone know if there is a way to make this work like the first call?
Thanks!
Mike

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 1

You cannot use a column name as a parameter for a table valued function. From Books Online...

Multi-statement Table-valued Functions

CREATE FUNCTION [ owner_name. ] function_name

( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] )

RETURNS @return_variable TABLE < table_type_definition >

[ WITH < function_option > [ [,] ...n ] ]

[ AS ]

BEGIN

function_body

RETURN

END

< function_option > ::=

{ ENCRYPTION | SCHEMABINDING }

< table_type_definition > :: =

( { column_definition | table_constraint } [ ,...n ] )

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

happycat59 One Orange Chip Points: 29451 More actions · Answer 2

You can do this with SQL 2005 using the APPLY operator

Sergiy SSC Guru Points: 110209 More actions · Answer 3

Change function GetFirstAndLastGiftInfo to a view and join it to table People on people_guid= people_guid

_____________
Code for TallyGenerator