Need function for drop temp table

Question

Post reply

Need function for drop temp table

Anand BI Developer

SSCommitted

Points: 1772
More actions
March 28, 2017 at 8:08 am

#322571

Hi Experts ,
i want to create a function for drop temp table. but below function got error while execute. please suggest me solution.
-- Temp table :
create table ##Anand(id int)
select * from ##Anand
-- Function
Alter function optimise_table_fn(@tab varchar(100))
returns varchar(100)
as
begin
        declare @check_tab varchar(100) = 'tempdb..'+@tab
     if OBJECT_ID(@check_tab) is not null
        begin
            declare @drop varchar(200) = ('drop table ' + @tab)
         EXECUTE sp_executesql @drop
        end
    return ( @tab + ' dropped_successfully');
end
-- Execute function :
select dbo.optimise_table_fn('##Anand')

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

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

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 1

You would need to use to stored procedure to do that not a function.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Anandkumar-SQL_Developer - Tuesday, March 28, 2017 8:08 AM
Hi Experts ,
i want to create a function for drop temp table. but below function got error while execute. please suggest me solution.
-- Temp table :
create table ##Anand(id int)
select * from ##Anand
-- Function
Alter function optimise_table_fn(@tab varchar(100))
returns varchar(100)
as
begin
        declare @check_tab varchar(100) = 'tempdb..'+@tab
     if OBJECT_ID(@check_tab) is not null
        begin
            declare @drop varchar(200) = ('drop table ' + @tab)
         EXECUTE sp_executesql @drop
        end
    return ( @tab + ' dropped_successfully');
end
-- Execute function :
select dbo.optimise_table_fn('##Anand')

What complexity might you hide by encapsulating your drop statement?

IF OBJECT_ID('TempDB..#MyLocalTempTable') IS NOT NULL DROP TABLE #MyLocalTempTable;

IF OBJECT_ID('TempDB..##MyGlobalTempTable') IS NOT NULL DROP TABLE ##MyGlobalTempTable;

Why are you using a global temp table?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

I see no benefit to creating a function to drop a table.

Creating the function just adds a layer of problems.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 4

ChrisM@Work - Tuesday, March 28, 2017 8:37 AM
Anandkumar-SQL_Developer - Tuesday, March 28, 2017 8:08 AM
Hi Experts ,
i want to create a function for drop temp table. but below function got error while execute. please suggest me solution.
-- Temp table :
create table ##Anand(id int)
select * from ##Anand
-- Function
Alter function optimise_table_fn(@tab varchar(100))
returns varchar(100)
as
begin
        declare @check_tab varchar(100) = 'tempdb..'+@tab
     if OBJECT_ID(@check_tab) is not null
        begin
            declare @drop varchar(200) = ('drop table ' + @tab)
         EXECUTE sp_executesql @drop
        end
    return ( @tab + ' dropped_successfully');
end
-- Execute function :
select dbo.optimise_table_fn('##Anand')
What complexity might you hide by encapsulating your drop statement?
IF OBJECT_ID('TempDB..#MyLocalTempTable') IS NOT NULL DROP TABLE #MyLocalTempTable;
IF OBJECT_ID('TempDB..##MyGlobalTempTable') IS NOT NULL DROP TABLE ##MyGlobalTempTable;
Why are you using a global temp table?

Just to be safe - you should pass in the second parameter to insure you are looking for tables:

IF OBJECT_ID('TempDB..#MyLocalTempTable', 'U') IS NOT NULL DROP TABLE #MyLocalTempTable;

IF OBJECT_ID('TempDB..##MyGlobalTempTable', 'U') IS NOT NULL DROP TABLE ##MyGlobalTempTable;

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs