Home Forums SQL Server 2005 Development Accessing data returned from a table valued function RE: Accessing data returned from a table valued function

  • Yes, you can do that, but you need to plan for what to do if the function returns more than 1 row, or make absolutely sure it can never do that.

    declare @var1 int, @var2 int;

    select @var1 = col1, @var2 = col2

    from dbo.MyFunction(input);

    Looks like that.

    But, keep in mind, if the UDF returns more than 1 row, you can't be sure which row the variables will get their values from. If you need to be sure, then there are ways to handle that.

    Also keep in mind, table-value UDFs are often a negative on SQL performance. Depends on how they're built, of course, but it's frequently something to move away from on busy systems or in performance-sensitive stored procedures.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon