Post reply

access temporary table in a user defined function ??

  • April 27, 2004 at 3:24 am

    #108167

    Hi All ,

     

    •      I have a store procedure SP1 who is using a temporary table #temp1
    •      I want to use this #temp1 table in a user defined function my_func_1

    For example 
     
    =============================================
    procedure SP1 
    ------
     
    create table #temp ( ....)
    begin
     
    insert into #temp( ) ....
    ---
             update #temp1
           set   res=  my_func_1( T.b , T.c )
           from #temp1 , aa
            where ....
    ----
     end
     
    ======================================
    create my_func_1( .... )
    begin
     
    ...
    return ( select  T.x + T.y
              from #temp1 , bb , cc
             where ..... )
     
     
     
    end
    ========================================
     
                      
     
    Thanks

    Georgia

  • April 28, 2004 at 6:06 am

    #504478

    you cannot use #temp table in the UDF that is created in the SP, because it is automatically dropped by the when SP ends.

     

    Imran

  • April 28, 2004 at 6:36 am

    #504481

    Actually, I do not believe you can even use Temporary Tables of the "##" type in a Function.  You can however Declare a Table Variable.

    Declare @Table Table(ID Int Primary, Value VarChar(50))

    Insert Into @Table Blah, Blah, Blah

  • April 28, 2004 at 11:02 pm

    #504598

    Hi

    You cannot access a table or temporary table from a user defined function

    Only tables local to the function can be accessed inside the function

    Anything global (cursors, tables) cannot be accessed inside a function

    Also built-in functions that are non-deterministic such as getdate also cannot be accessed

    For more information you can see SQL Server Books Online

    under the topic UDF

    Regards

    Shiva

  • April 29, 2004 at 6:35 am

    #504652

    I don't think mshiva is entirely correct.  I am able to access Tables from a Function.  Of course, these are "Table Valued" Functions and not "Scaler-Valued" Functions, but I don't see why you would be able to in a Scaler Function also.  He is correct about the "GetDate" though, it cannot be used.

    At one time, I did need your requirement...to access a Temp Table.  I found that Functions could only Access Tables that are already Created in the Database.  Here's a snipet from a Function that I know works...I use it a lot!

    CREATE FUNCTION dbo.udf_GetValues
/*
 Returns a Table with the Values for a specific 'Reference'
 
 Parameters:
  @Constant - The Constant Variable for the Reference
  @Deleted - 
   When 0, Returns only Active Values
   When 1, Returns only Deleted Values
   When NULL, Returns All Values
*/
( 
     -- Parameters
     @Constant VarChar(50), @Deleted TinyInt
)
RETURNS 
 @Table TABLE (
 ValueID Int, ValueOrder SmallInt, ValueName VarChar(50), ValueDescription VarChar(255), Constant VarChar(50), 
 CreatedBy VarChar(50), CreatedDate SmallDateTime, ModifiedBy VarChar(50), ModifiedDate SmallDateTime, Deleted TinyInt)
AS
BEGIN
 If (@Deleted Is Null)
  BEGIN
  Insert Into @Table
   Select ValueID, ValueOrder, ValueName, ValueDescription, Constant, CreatedBy, CreatedDate, ModifiedBy, ModifiedDate, Deleted
   From [dbo].[ReferenceValues] WITH (NOLOCK)
   Where (ReferenceID IN(Select ReferenceID From [dbo].[ReferenceLists] Where Constant = @Constant))
   Order By ValueOrder, ValueName
  END
 Else
  BEGIN
   Insert Into @Table
   Select ValueID, ValueOrder, ValueName, ValueDescription, Constant, CreatedBy, CreatedDate, ModifiedBy, ModifiedDate, Deleted
   From [dbo].[ReferenceValues] WITH (NOLOCK)
   Where (ReferenceID IN(Select ReferenceID From [dbo].[ReferenceLists] Where Constant = @Constant)) And (Deleted = @Deleted)
   Order By ValueOrder, ValueName
  END
 RETURN
END

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply