SQL Server :

29 Dec 2005 8:31 PM

Sirisha

Hi,

I have created a table valued user defined function

CREATE FUNCTION Testting (@labrunid int)
    RETURNS TABLE
AS
RETURN
    (
        SELECT    *
         From labruns
        where labrunid = @labrunid
    )

Now in the Query Analyzer when i try this

select labrunid from dbo.Testting(909) it wroks perfefctly fine.

But when i wnat to use something like this

select labrunid from dbo.Testting(labrunid) I am getting

'labrunid' is not a recognized OPTIMIZER LOCK HINTS option.

I wnat to use this so that i want to use a function in the view that would
take the parameter from the select and will generate me a report.

Am i missing anything

Thank you

--
Sirisha

29 Dec 2005 8:34 PM

Sirisha

--
Sirisha

Show quote

"Sirisha" wrote:

> Hi,
>
>   I have created a table valued user defined function
>
> CREATE FUNCTION Testting (@labrunid int)
>     RETURNS TABLE
> AS
> RETURN
>     (
>         SELECT    *
>          From labruns
>         where labrunid = @labrunid
>     )
>
> Now in the Query Analyzer when i try this
>
> select labrunid from dbo.Testting(909) it wroks perfefctly fine.
>
> But when i wnat to use something like this
>
> select labrunid from dbo.Testting(labrunid) I am getting
>
> 'labrunid' is not a recognized OPTIMIZER LOCK HINTS option.
>
> I wnat to use this so that i want to use a function in the view that would
> take the parameter from the select and will generate me a report.
>
> Am i missing anything
>
> Thank you
>
>
>
>
> --
> Sirisha

29 Dec 2005 8:37 PM

David Browne

Show quote

"Sirisha" <spusap***@hotmail.com> wrote in message
news:9C07729C-4B16-46CB-80D4-C668A1AFCC81@microsoft.com...
> Hi,
>
> I have created a table valued user defined function
>
> CREATE FUNCTION Testting (@labrunid int)
>    RETURNS TABLE
> AS
> RETURN
>    (
>        SELECT    *
>         From labruns
>        where labrunid = @labrunid
>    )
>
> Now in the Query Analyzer when i try this
>
> select labrunid from dbo.Testting(909) it wroks perfefctly fine.
>
> But when i wnat to use something like this
>
> select labrunid from dbo.Testting(labrunid) I am getting
>
> 'labrunid' is not a recognized OPTIMIZER LOCK HINTS option.
>
> I wnat to use this so that i want to use a function in the view that would
> take the parameter from the select and will generate me a report.
>
> Am i missing anything
>

Using a column from a select as the input to a table-valued UDF requires use
of the CROSS APPLY or OUTER APPLY operators in SQL Server 2005.

http://msdn2.microsoft.com/ms177634.aspx

David

29 Dec 2005 8:51 PM

Sirisha

Hi David,

I am using SQL server 2000. is there any solution for that version???

--
Sirisha

Show quote

"David Browne" wrote:

>
> "Sirisha" <spusap***@hotmail.com> wrote in message
> news:9C07729C-4B16-46CB-80D4-C668A1AFCC81@microsoft.com...
> > Hi,
> >
> > I have created a table valued user defined function
> >
> > CREATE FUNCTION Testting (@labrunid int)
> >    RETURNS TABLE
> > AS
> > RETURN
> >    (
> >        SELECT    *
> >         From labruns
> >        where labrunid = @labrunid
> >    )
> >
> > Now in the Query Analyzer when i try this
> >
> > select labrunid from dbo.Testting(909) it wroks perfefctly fine.
> >
> > But when i wnat to use something like this
> >
> > select labrunid from dbo.Testting(labrunid) I am getting
> >
> > 'labrunid' is not a recognized OPTIMIZER LOCK HINTS option.
> >
> > I wnat to use this so that i want to use a function in the view that would
> > take the parameter from the select and will generate me a report.
> >
> > Am i missing anything
> >
>
> Using a column from a select as the input to a table-valued UDF requires use
> of the CROSS APPLY or OUTER APPLY operators in SQL Server 2005.
>
> http://msdn2.microsoft.com/ms177634.aspx
>
> David
>
>
>

29 Dec 2005 9:17 PM

Anith Sen

You cannot use column names to parameters to inline table valued UDFs. The
alternative is to use a correlated subquery ( or use other SQL facilities
like derived table, join etc ).

--
Anith