.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Temporary table in table-valued functions can not be dropped

Posted By:      Posted Date: December 04, 2010    Points: 0   Category :ASP.Net


I have created a table-valued function like:

   @List nvarchar(max)
RETURNS @RtnValue table
   column varchar(255)

SELECT column
   SELECT column
) AS #C
ON A.column = #C.column
WHERE column = @List


However, I find that the #C won't be dropped in the tempdb after the function execution. It can't be remove unless I restarted the server.


If I added:

Drop Table #C

in the function, there will be an error message saying "Cannot access temporary tables from within a function".


Please advice/suggest how can I remove the temp table after the function execution. I am using MSSQL 2005.

P.S. I know that I can use table variable instead, but I don't want to do this since it will involve lots of changes for me. I wish I can just drop the temp table by script or "manually".






View Complete Post

More Related Resource Links

CLR Table Valued Functions works in Visual Studio but gets permissions errors when I try to use in S


This is a CLR question but here's some backgound first:

I need to talk to a Sybase "Advantage" database server and join results together with some SQL Server data.  I tried using Enterprise manager to link a server to Advantage using both OLE DB and ODBC.  Both types of connections Ping ok when I create then but they also both error out when, from within the linked server screen in enterprise manager, I try to open the tree control to view tables in the database.

I also tried to access the Advantage server using an opendatasource call through both OLE DB and ODBC - both calls errored out with "Invalid object name"

I read the Advantage documentation and I discovered that the drivers - at least the ODBC driver - are a combination of level 1 and level 3 compliance, so I concluded that SQL Server is bombing when it tries to retrieve catalog information after it connects...


I coded up a C# table valued function to retrieve the data that I need using ODBC.  The function works fine from within Visual Studio but now I'm getting CAS errors when I try to use it from within SQL Server.  The issue is security and I'm having trouble figuring out how to tune permissions etc. to get this to work.  Specifically I am getting this error when I try to call t

Insert value using Table Value Functions

a real gem in Sql Server 2008. mostly people still using Stored procedure may be they shifted to SQL Server but they are not using TVF right now.

Temporary tables in SQL Server vs. table variables

When writing T-SQL code, you often need a table in which to store data temporarily when it comes time to execute that code. You have four table options: normal tables, local temporary tables, global temporary tables and table variables. I'll discuss the differences between using temporary tables in SQL Server versus table variables.

Creating a table-valued function on a user defined type in SQL Server CLR

We would like to be able to create a table-valued function on a user defined type.  We would like the syntax in SQL to look like it works for the XML nodes function:   DECLARE @myXml XML = '<a><b>1</b><b>2</b><b>3</b></a>' ; SELECT  node.query('text()') FROM    @myXml.nodes('a/b') nodes (node) ;   In other words, in the FROM clause, we can access the "nodes" method of the xml variable and it returns a rowset. Thanks!

Which connection owns global temporary table?

Global temporary table exists over time of connection in which was created. I have several connections (sessions) open in management studio. How to check using sql statement, system views, etc... if global temporary table was created in current session?

How to insert values into my table the data is coming from inline table valued function

Hi all,   i have table oi want insert values into my table the data is coming from inline table valued function how i can use the inline table valued function to insert datainto my table i have inline function which out puts 3 collumns of data how i can insert this data into my table i have the insert statment like below insert into tablename(c1,c2,c3,c4,c5) select ('abc',....................,'xyz') how i can use the out put put of my inline function to  to insert data into my columns c2,c3,c4. please help me out Thanks & Regards Sunil Yoganna

table valued function problem

This is my table-valued function Problem is it is returning only 1 record but it is supposed to return more than one i stuck with this problem plz replyALTER function [dbo].[funcqidata](@dst int,@mth int,@yer int) returns @cqi_rpt table ( hosp_name varchar(100) NULL, bedded_strength int NULL, out_patients int NULL, in_patients int NULL, minor_surgeries int NULL, major_surgeries int NULL, deliveries int NULL, radio int NULL, lab_test int NULL, tot_part1 int NULL, waste_man int NULL, swab_test int NULL, attendence int NULL, drug int NULL, charges_thismonth int NULL, charges_lastmonth int NULL, usr_charges_marks int NULL, tot_part2 int NULL --grand_tot int NULL ) as BEGIN DECLARE @hosp_name varchar(100), @bedded_strength int, @out_patients int, @in_patients int, @minor_surgeries int, @major_surgeries int, @deliveries int, @radio int, @lab_test int, @tot_part1 int, @opd_handled int, @opd_target int, --------- supporting variables declared for calculation ----------- @ipd_handled int, @ipd_target int, @minor_handled int, @minor_target int, @major_handled int, @major_target int, @delivery_handled int, @delivery_target int, @radio_handled int, @radio_target int, @lab_handled int, @lab_target int, -------- end of declaring variables ----------------------------- @check_charges int, @waste_man int, @swab_test int,

trying to use CASE within table-valued function

Hi, I am trying to create a table-valued function that takes a string argument and, depending upon it's value, returns one of a number of identically-structured tables (and before someone asks why multiple identically structured tables, NOT my idea!).  I am a novice with T-SQL, but I've been attempting something like this for the function: Case @Input   When 's1' then return table1   When 's1' then return table2 End ... but I keep getting errors.  Can someone help get the ball rolling and show me the basics on how to set up a function to do this?  I'm hoping since I'm just redirecting to existing tables that this function will have minimal performance hits.

Cannot call Table Valued Function from Dynamic stored procedure statement

Hello,  I have a table-valued function that splits string into a table column. I can easily call this function from a stored procedure within a regular SELECT statement: SELECT * FROM Table1 WHERE Code1 = '1' AND Code2 IN(SELECT * FROM [dbo].[fnSplitValues](@Code2String))   However when I try to use the same logic for a dynamic SELECT statement: ‘SELECT * FROM Table1 WHERE Code1 = '1' AND Code2 IN(SELECT * FROM ' + [dbo].[fnSplitValues](@Code2String) + ')’ I get an error ‘Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fnSplitValues", or the name is ambiguous.’ Any idea what is wrong with my dynamic SQL?   Please help, Lana

Schema comparer mistake in table-valued function (Nullability in column definition)

Hello. In my database i have table valued function. In its definition nullability of column is not set directly. This column has a user-defined data type. So the question is: is this column nullable? As i can see here: http://msdn.microsoft.com/en-us/library/ms174979.aspx   When column nullability is not explicitly specified, column nullability follows the rules shown in the following table. Column data type Rule Alias data type The Database Engine uses the nullability that is specified when the data type was created. To determine the default nullability of the data type, use sp_help. It should be not null, as user defined type is declared as not null. SMO says, that column is not null, but schema compare says it is nullable. Who is wrong and why?

How to create a temporary table in C#?

Hello Friends,Can i make a bulk insert into the temporary table by calling a stored procedure from the front end.Am using BulkCopy command to write data to server bulkcopy.destinationtablename="#TempTableName";(here i want to give the tempoary tablename which is created in the database)bulkcopy.Writetoserver(datatable dt);How to do this?(OR)Is there anyway to create a temporary table from C# and set the destinationtable as #TemPTableNameand then write to server.Please do the needful.Thanks,Suchitra

Inline table valued function, full outer join and cross apply

Good afternoon, I'm experiencing quite strange issue with our T-SQL code. We have inline table-valued function which is using full outer join on 1 = 1. The reason why we do it this way is - we need to combine one row from 2 different result sets either of them can have either 0 or 1 row. The strange thing, that if we have the situation when both row-sets are empty, function does not return the row (as expected). Although, select which uses this function with cross apply "uses" the result from the previous row. Below is the simplified example. First select with ID = 2 predicate does not return any rows. Although if I remove this filter, it re-uses Value2 from the previous row. create FUNCTION dbo.Func(@Id int) RETURNS TABLE AS RETURN ( with CTE1(Value1) as ( select 'Value1' where @ID = 1 ) ,CTE2(Value2) as ( select 'Value2' from CTE1 ) select CTE1.Value1, CTE2.Value2 from CTE1 full outer join CTE2 on 1 = 1 ) go select * from dbo.Func(2) go create table dbo.TestTable ( ID int not null ) go insert into dbo.TestTable values(1), (2) go select t.ID, f.Value1, f.Value2 from dbo.TestTable t cross apply dbo.Func(t.ID) f where t.ID = 2 go select t.ID, f.Value1, f.Value2 from dbo.TestTable t cross apply dbo.Func(t.ID) f order by t.id go Our SQL version is: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) &nb

using table valued parameters for stored procedures any limitations

I am thinking of using a table valued parameter for my new stored procedure, but wonder if there are any performance implications to be aware of and is the limitation of using statistics applicable with table value parameters, i.e do they keep statistics and will this affect the judgement of query plans i.e estimated number of rows, from my experience table variables do suffer from this problem but I would like to confirm if it applies to table value parameters as well.

Table Valued Parameters (TVP)


hi all;

this is new to me but i want to have a TVP to accept list of records as a parameter of a stored procedure. the following is the code i have written.

CREATE TABLE dbo.GroupRole
userRole VARCHAR(100) NOT NULL,


CREATE PROCEDURE grpRoleRecorder (@userRole usrRoleArr READONLY)
 INSERT INTO dbo.GroupRole VALUES (@userRole);

when i execute the code it gives the folling error.

Msg 137, Level 16, State 1, Procedure grpRoleRecorder, Line 4
Must declare the scalar variable "@userRole".

Please help me on this.

Thank You All

using a checkbox list and Table Valued Parameters


I have a checkboxlist that gets all all the items from a look up table. The user is going to go to the list, check certail items and insert the checkboxid, the value (0 or 1),  and the userid  into a table valued paramater using a stored procedure. I have the user defined table type created but thats about it. Any body out there have any hints.

OPENQUERY throws Error 7357 when the source SP uses temporary table.

Hello Everybody / Anybody,
Sorry but exiting problem!
The Problem: OPENQUERY throwing error [Error 7357]when the source SP uses temporary table.
Description : Need to validate data against master list. My combo on UI has a source Stored Proc(contains a temp table in it).
I'm importing data from Excel. Before import, I want to validate it against my master list values.
[say field Priority has master values "High, Medium,Low".] and in excel user has added 'ComplexHigh' under priority field]
In this case, my import validator StoredProc should not accept value 'ComplexHigh' as it is not present in my Priority master list]
I'm preparing a temp table tabName  containing o/p of SP, it works fine zakkas if my SP  usp_SelectData does not contain temp table. 
I think you got what the situation is!! Woh!

Using ADO.NET SqlDbType.Structured TVP (Table Valued Parameters) causes SQL Compilation for every ca


When calling a stored procedure with a table valued parameter from ADO.Net, each call requires a SQL Compilation.  In an application that makes this call thousands of times per second, this causes SQL Compilations thousands of times per second leading to degraded performance.

When calling the same stored procedure directly from Management Studio, no SQL Compilations are performed.

Steps to reproduce ...

1. Create the following User Defined Type and Stored Procedure (SQL2008 required) ...

CREATE TYPE [dbo].[BigIntListType] AS TABLE(
 [Id] [bigint] NOT NULL,
  [Id] ASC

  @itemIds AS BigIntListType READONLY
  SELECT col1, col2 FROM table1
  JOIN @itemIds ON table1.id = @itemIds.Id

2. Start SQL Profiler and capture "Showplan XML for Query Compile".  Start Perfmon and add "SQL Compilations / Second"

3. Run the following code from Management Studio as many times as possible ...

declare @p2 dbo.BigIntListType

insert into @p2 values(601)
insert into @p2 values(1)

exec testProc @itemIds = @p2

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend