Problem performing a join on a function in a SQL query

Hello,

Can someone explain why this code contains the following error:

Msg 4104, Level 16, State 1, Line 2

The multi-part identifier "TheTable.StartValue" could not be bound.

CREATE FUNCTION MyFunction(@.StartValue int)

RETURNS @.MyTable TABLE

(

NextValue int NOT NULL

)

AS

BEGIN

INSERT INTO @.MyTable(NextValue)

VALUES (@.StartValue + 1)

INSERT INTO @.MyTable(NextValue)

VALUES (@.StartValue + 2)

RETURN

END

GO

CREATE TABLE TheTable

(

StartValue int NOT NULL

)

GO

INSERT INTO TheTable(StartValue)

VALUES (10)

INSERT INTO TheTable(StartValue)

VALUES (20)

GO

SELECT *

FROM TheTable CROSS JOIN

MyFunction(TheTable.StartValue)

You can′t do that per row. The logic is quite simple that you presented here, what about doing

SELECT StartValue, StartValue+1,StartValue+2
From SomeTable

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

In SQL Server 2000, this is not possible. However, in 2005, you can use the CROSS APPLY join operator:

SELECT *
FROM TheTable
CROSS APPLY MyFunction(TheTable.StartValue)

Interesting function. If you don't mind, could you share the purpose?

|||

Hi,

you cannot use Table's Column as a parameter to the function. Only variables or Static Literals can be passed as an argument to the function

|||

Thanks for your reply.

I wrote that function as an example of what I was trying to do. I have a vertical bar delimited column (eg. this|is|my|column). I used a CLR function to get all the values. I then run a aggregate of these values based on another field in another table. So the output should be something like this.

this: 2
is: 4
my: 0
column: 1

I can't do a straight aggregate because the "my" values above would be omitted.

|||

Thanks for your reply.

I wrote that function as an example of what I was trying to do. I have a vertical bar delimited column (eg. this|is|my|column). I used a CLR function to get all the values. I then run a aggregate of these values based on another field in another table. So the output should be something like this.

this: 2
is: 4
my: 0
column: 1

I can't do a straight aggregate because the "my" values above would be omitted.