Problem passing a variable into a table-valued function

Hi,

i am encountering a problem in a stored procedure when a pass a variable value into a table-valued function. The table-valued function is named getCurrentDriver and has 1 attribute: car-ID.

The syntax is as follows:

select car.id, car.licenceNumber, car.brand, car.model,
(select driverName from getCurrentDriver(car.id)) as driverName
from car

When I try to compile I get following error on the line of the function:
Incorrect syntax near '.'

The database version is SQL Server 2000 SP3.

What am I doing wrong? Is there a workaround for this error?select car.id, car.licenceNumber, car.brand, car.model,
dbo.getCurrentDriver(car.id) as driverName
from car|||[sniped]

select car.id, car.licenceNumber, car.brand, car.model,
, dbo.getCurrentDriver(car.id) as driverName
from car

??|||The problem is that he is putting a table-valued function in the select clause. This is not allowed:

select car.id,
car.licenceNumber,
car.brand,
car.model,
(select driverName
from getCurrentDriver(car.id)) as driverName
from car

TBP, you need to JOIN to the results of a table function as if it were a table or a view:
Post the code for getCurrentDriver(), and we can help you out. Maybe you should be using a scalar function instead...|||dote. had'nt thought about that.|||also, part of the problem is that "license" is spelled wrong ;)|||Good eye. That would certainly not get past SQL Server 2005's Spell Checker.|||The problem is that he is putting a table-valued function in the select clause. This is not allowed:

select car.id,
car.licenceNumber,
car.brand,
car.model,
(select driverName
from getCurrentDriver(car.id)) as driverName
from car

TBP, you need to JOIN to the results of a table function as if it were a table or a view:
Post the code for getCurrentDriver(), and we can help you out. Maybe you should be using a scalar function instead...

Hi Blindman,
are you sure you can't use table-defined function in a select clause?
The syntax works when I do this:

declare @.CarID int
select @.CarID = 123

select car.id,
car.licenseNumber,
car.brand,
car.model,
(select driverName
from getCurrentDriver(@.CarID)) as driverName
from car
where car.id = @.CarID

The function getCurrentDriver is very straightforward and is tested successfully.
It seems to be a bug in SQL Server 2000 but I'm not sure...|||Good eye. That would certainly not get past SQL Server 2005's Spell Checker.
It's certainly fun trying to write SQL for tables whose columns are called "identifer" and "sirname"|||Hi Blindman,
are you sure you can't use table-defined function in a select clause?
The syntax works when I do this:

declare @.CarID int
select @.CarID = 123

select car.id,
car.licenseNumber,
car.brand,
car.model,
(select driverName
from getCurrentDriver(@.CarID)) as driverName
from car
where car.id = @.CarID

The function getCurrentDriver is very straightforward and is tested successfully.
It seems to be a bug in SQL Server 2000 but I'm not sure...
What do you expect to happen if your table function returns more than one record or more than one column? And if it always returns one record and one column, then it is a scalar function and should be defined as such.|||Hasn't this something to do with the missing schema name (owner in SQL 2000) when calling the function? Althought it beats me why the @.CarID example seems to work.

select car.id,
car.licenceNumber,
car.brand,
car.model,
(select driverName
from dbo.getCurrentDriver(car.id)) as driverName
from carsql