How do I use a UserDefinedFunction in a View?

22 Dec 2005 5:12 PM

James

I have a UDF that takes one single INT parameter and returns a Table like
So::

CREATE PROCEDURE dbo.TestUDF
(
    @aParam int
)
AS
    BEGIN
        CREATE TABLE #TestTempTable ( a INT PRIMARY KEY, b INT, c INT )
        INSERT INTO #TestTempTable VALUES ( 1, 2, 3 )
        INSERT INTO #TestTempTable VALUES ( 2, 3, 4 )
        INSERT INTO #TestTempTable VALUES ( 3, 4, 5 )
        SELECT * FROM #TreePath
        GO
    END

Now, how can I add this to a View as a a Detail Table i.e. the right side of
a join Like So?

CREATE VIEW MyTestView AS
SELECT     dbo.MasterTable.*,
FROM         dbo.MasterTable LEFT OUTER JOIN
                      dbo.TestUDF() dbo.TestUDF ON dbo.MasterTable.pk =
dbo.TestUDF.b

I see that TestUDF() is going to need to get its @aParam set somehow but
shouldn't the join do it for me?

If I type a value into the parens like so TestUDF ( 1 ) then it will work
but that is then hard coded and not dynamic as it should be. How can I do
what I am trying to do. I want to treat the returned TABLE as though it
were areal DB table and JOIN on it.

thanks,

JIM

22 Dec 2005 5:21 PM

James

By the way, I meant FUNCTION not PROCEDURE when I typed this but the issue
is the same.

JIM

Show quote

"James" <nospam@hypercon.net> wrote in message
news:%23YFFlrxBGHA.2920@tk2msftngp13.phx.gbl...
>I have a UDF that takes one single INT parameter and returns a Table like
>So::
>
> CREATE PROCEDURE dbo.TestUDF
> (
>    @aParam int
> )
> AS
>    BEGIN
>        CREATE TABLE #TestTempTable ( a INT PRIMARY KEY, b INT, c INT )
>        INSERT INTO #TestTempTable VALUES ( 1, 2, 3 )
>        INSERT INTO #TestTempTable VALUES ( 2, 3, 4 )
>        INSERT INTO #TestTempTable VALUES ( 3, 4, 5 )
>        SELECT * FROM #TreePath
>        GO
>    END
>
> Now, how can I add this to a View as a a Detail Table i.e. the right side
> of a join Like So?
>
> CREATE VIEW MyTestView AS
> SELECT     dbo.MasterTable.*,
> FROM         dbo.MasterTable LEFT OUTER JOIN
>                      dbo.TestUDF() dbo.TestUDF ON dbo.MasterTable.pk =
> dbo.TestUDF.b
>
> I see that TestUDF() is going to need to get its @aParam set somehow but
> shouldn't the join do it for me?
>
> If I type a value into the parens like so TestUDF ( 1 ) then it will work
> but that is then hard coded and not dynamic as it should be. How can I do
> what I am trying to do. I want to treat the returned TABLE as though it
> were areal DB table and JOIN on it.
>
> thanks,
>
>
> JIM
>
>
>

22 Dec 2005 5:33 PM

Aaron Bertrand [SQL Server MVP]

> I see that TestUDF() is going to need to get its @aParam set somehow but
> shouldn't the join do it for me?

Should it guess what value you want to pass? Do you want a value from
MasterTable? If so, which column?

A

22 Dec 2005 5:39 PM

James

Yes, it should be the detail side in a typical Master Detail left outer JOIN
like so

SELECT     dbo.MasterTable.*, dbo.TestUDF.*
FROM         dbo.MasterTable LEFT OUTER JOIN
                      dbo.TestUDF() dbo.TestUDF ON dbo.MasterTable.pk =
dbo.TestUDF.b

I think from this you can see that I am linking on MasterTable.pk

Thanks,

JIM

Show quote

"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:uEDYs1xBGHA.3156@TK2MSFTNGP12.phx.gbl...
>> I see that TestUDF() is going to need to get its @aParam set somehow but
>> shouldn't the join do it for me?
>
> Should it guess what value you want to pass? Do you want a value from
> MasterTable? If so, which column?
>
> A
>

22 Dec 2005 11:30 PM

ML

For table valued functions to be used in the FROM clause all parameters must
be passed as literals - referencing columns form joined tables is not
allowed (and will be recognised by the parser as join hints).

In SQL 2005 you can use the APPLY operator.

ML

---
http://milambda.blogspot.com/