user-defined function return table with dynamic sql?

user-defined function return table with dynamic sql?

Post by Peter Lindgre » Fri, 06 Dec 2002 06:30:35



Hello!
I have a user-defined function that needs to return a table. The select statement must be dynamic. How do I accomplish this? (this udf is used from another stored procedure)
The dynamic query selects records from a third-party database (Jet, as a linked server, whose name is dynamic).
I have SQL Server 2000. Here's what I've got so far:

ALTER  FUNCTION dbo.qryCustomerGeneralArtGroupRebates
(

)

(
   ArtikelNr     nvarchar(50),
   Artikelgrupp  int
)
AS BEGIN










   -- cannot EXEC inside a function, either :-(
   RETURN
END

 
 
 

user-defined function return table with dynamic sql?

Post by Uri Diman » Fri, 06 Dec 2002 17:22:35


Peter
You cannot use dynamic sql with UDF


Quote:> Hello!
> I have a user-defined function that needs to return a table. The select

statement must be dynamic. How do I accomplish this? (this udf is used from
another stored procedure)
Quote:> The dynamic query selects records from a third-party database (Jet, as a

linked server, whose name is dynamic).
> I have SQL Server 2000. Here's what I've got so far:

> ALTER  FUNCTION dbo.qryCustomerGeneralArtGroupRebates
> (

> )

> (
>    ArtikelNr     nvarchar(50),
>    Artikelgrupp  int
> )
> AS BEGIN










is!!!
>    -- cannot EXEC inside a function, either :-(
>    RETURN
> END