Temp Tables & UDFs - SQL Server 2000

[elmo29]

I have a question. You cannot use Temp tables in a UDF. What are the implications of this??

I wanted to created a UDF which needed to return a table based of several tables created on the fly. I made them temp tables as each connection has different data created on the fly - and obviously I don't want different connections accessing each others data.

I am assuming a UDF does not ensure this. Am I right?

Thanks

Elmo

 

[tlbroadbent]

Use table variables instead of temp tables. A UDF can even return a table. See SQL BOL for details. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[elmo29]

WOuld these work in a similar way to temp tables - i.e. private to the connection only??

 

[essa2000]

Dear ;

Yes, it is private for the connection.

Here are remarks about Table Variable from BOL.

Remarks

Functions and variables can be declared to be of type table. table variables can be used in functions, stored procedures, and batches.

Use table variables instead of temporary tables, whenever possible. table variables provide the following benefits:

A table variable behaves like a local variable. It has a well-defined scope, which is the function, stored procedure, or batch in which it is declared.
Within its scope, a table variable may be used like a regular table. It may be applied anywhere a table or table expression is used in SELECT, INSERT, UPDATE, and DELETE statements. However, table may not be used in the following statements:

INSERT INTO table_variable EXEC stored_procedure

SELECT select_list INTO table_variable statements.

table variables are cleaned up automatically at the end of the function, stored procedure, or batch in which they are defined.

table variables used in stored procedures result in fewer recompilations of the stored procedures than when temporary tables are used.


Transactions involving table variables last only for the duration of an update on the table variable. Thus, table variables require less locking and logging resources.


Regards,
Muhammad Essa Mughal