pass table fields as parameters to table-valued function

[samvb]

Hey ALL,
I have the following table-valued functions that works well if I pass its arguments with literal numbers or from declared variables.

USE [db]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[returnowner]
(@type smallint, @typeid int )
RETURNS @RESULTS TABLE (OwnerName varchar(500))
AS
BEGIN
DECLARE @Value varchar(100)
if @type=0 --owned by company
BEGIN
SELECT @Value='Company'
INSERT INTO @RESULTS (OwnerName)
SELECT @Value
END 
else if @type=1 --owned by a specific head
BEGIN
SELECT @Value=headname FROM tblHeads WHERE headid=@typeid
INSERT INTO @RESULTS (OwnerName)
SELECT @Value
END 


RETURN
END

Is there any way to pass the values right from table and have its result return part of a select statment, as follows for e.g.

SELECT * tbl1,
   returnowner.ownername 
FROM dbo.returnowner(tbl1.type,tbl1.id)

tbl1 contains a list in such a way :

type id
0 0
1 12
1 14
1 8

1 12 means the row is head and so the function would get name of a head with id 12. The error i am getting from sql server is err 4104

"The multi-part identifier "tbl1.type" could not be bound."

I thought of using case in this way:

[case]
case type
WHEN 1 THEN SELECT headname FROM tblheads WHERE headid=@typeid
[/case]

But it is incorrect basically. Are there any alternatives?

[NeoPa]

I see nothing in your SQL that indicates what you hope to do with the data from tbl1. You seem to treat it as a field in your SELECT statement, but that makes no sense at all, so trying to interpret, from that, what it is you're actually trying to do, is impossible.

Generally, tables are added to queries in the FROM clause. In most cases with some type of JOIN to any other tables there.

[Rabbit]

While it is possible to pass a table variable and return a table variable. It makes no sense to do so in your case.

In fact, you don't even need to use a custom function or even an expression. You should just create a lookup table and join to that.

[samvb]

Actually, i really would need the future as the real design goes beyound the sample i gave u but here it is anyway: a car could be owned by an employee,a department, a division or a unit. I thot of creating for look tables [caremployees,ca rdepartments etc] in which case a car could exist only in one place. but that would also mean looking for it in all 4 tables during registration and when gettings its location. I thot of adding a field "ownertype" (.e.g 0=employee,1=de partment). Based on that value, I would at least go to the right table and search for the car.

Thats the initial idea that led me to do custom function.

[NeoPa]

SamVB:
Actually, i really would need the future as the real design goes beyound the sample i gave u but here it is anyway:

What? I don't even know what this means. Are you translating this from another language?

I tried to make sense of your post a number of times but gave up. If you are translating from another language then I regret my inability to make sense of what you're trying to say. I know I couldn't do a better job in any other language, but that doesn't help me to help you I'm afraid.

[Rabbit]

There's no reason that you can't design a database that allows for a "car" to be at multiple "locations" .

As for having to join to multiple tables to retrieve the data you need, that is the entire purpose of a relational database.

It sounds as if you're trying to work around perceived draw backs when there are no such draw backs and is actually required for good design.