Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Help !! Drop Table from Procedure

Author  Topic 

akki
Starting Member

14 Posts
Posted - 2003-07-02 : 02:18:53
I want to write a procedure that Drops a given table. I have written following procedure but am getting Error170: Line 5: Incorrect Syntax near '@TName'.

CREATE PROCEDURE DBO.Drop1
@TName varchar(255)
AS
IF EXISTS(SELECT name FROM sysobjects WHERE name = @TName)
DROP table @TName
GO

I am new SQL please help !!.

--akki

Andraax
Aged Yak Warrior

790 Posts
Posted - 2003-07-02 : 02:24:08
You'll have to use dynamic SQL. E.g:

declare @cmd varchar(100)
select @cmd='DROP table ' + @TName

exec (@cmd)
Go to Top of Page

akki
Starting Member

14 Posts
Posted - 2003-07-02 : 02:57:37
Thank you Andraax

--akki
Go to Top of Page

Amethystium
Aged Yak Warrior

701 Posts
Posted - 2003-07-02 : 04:50:03
quote:


IF EXISTS(SELECT name FROM sysobjects WHERE name = @TName)
	DROP table  @TName



Careful here as you might end up dropping a system table by mistake. It is a good idea to include the following extra condition in your code.

IF EXISTS(SELECT name FROM sysobjects WHERE name = @TName  AND xtype = 'U')
	DROP table  @TName



....this way, you will know for sure that it will only drop a user type table. Not essential, but a sure way of preventing catastrophes!
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2003-07-02 : 10:23:51
Well...Why not do DROP DATABASE @dbName


Really, I would Highly nor reccommend what you're doing..

You need to be in more control

I'm assuming that if you're dynamically dropping, that you are also dynamically creating...

Why not have 1 physical table a,d add a userid and manage the rows with inserts and deletes?



Brett

8-)
Go to Top of Page

mr_mist
Grunnio

1870 Posts
Posted - 2003-07-02 : 10:45:08
quote:


I am new SQL please help !!.



Ahh, then this is the perfect time to stop what you are doing and go in another direction. Dynamically dropping tables will eventually lead to a world of pain known as the dark side, where your permissions go out the window and noone has a clue what is where and which way is up.

Keep order, there must be another way to achieve what you want to achieve.

-------
Moo.

Edited by - mr_mist on 07/02/2003 10:45:53
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2003-07-02 : 10:59:49
And a bovine MOO for Mr. Mist with his sage advice.
...

The dark side ...LOL


"Luke...I am your father"

NOOOOOOOOOOOO it can't be true...



Brett

8-)
Go to Top of Page

Andraax
Aged Yak Warrior

790 Posts
Posted - 2003-07-02 : 11:05:49
Hey guys now don't blame me... I only answer questions... I have no morality whatsoever

Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2003-07-02 : 11:11:10
quote:

Hey guys now don't blame me... I only answer questions... I have no morality whatsoever




Hey me too...guilty as charged...giving answers to things that should be re-engineered..

But hey if they need it, who am I? I usually preface it with "It's not a good idea".

And I always distgusted afterwards...but hey an addicition is an additicion..



Brett

8-)

Edited by - x002548 on 07/02/2003 11:12:18
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2003-07-02 : 11:51:40
It can't be stressed enough that what you are doing is very, very bad. You should never write a stored procedure that can dynamically drop objects. What will stop a user that has execute privileges on the stored procedures from dropping a base table? Well constraints might stop him/her, but if he/she tried hard enough, then all tables could be deleted easily from your production database. Even if you only grant EXECUTE to certain users, I still would not recommend doing this.

You've been warned.

Tara
Go to Top of Page

Amethystium
Aged Yak Warrior

701 Posts
Posted - 2003-07-02 : 11:59:05
quote:

You've been warned.
Tara


I bet my life you're a DBA. You must be!
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2003-07-02 : 12:07:15
Yes I am, a production DBA as well as a development DBA. So not only do I get to see the development cycle, but I also get to see some not so good ideas get implemented in production and then have to support them. You would think that the not so good ideas wouldn't make it into production, but you can only warn them so many times.

Tara
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2003-07-02 : 13:15:42
Sometimes I feel you have to tell them not to run with scissors...



Brett

8-)
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2003-07-02 : 13:22:59 


CREATE PROC myDangerousSproc 
	@name sysname @objecttype varchar(25) 
AS
	DECLARE @sql varchar(8000)

	BEGIN TRAN

	@sql = 'DROP '+ @objecttype+ ' ' + @name

	EXEC(@sql)

	IF @@Error <> 0
		BEGIN
		ROLLBACK TRAN
		RETURN -1
		END
	ELSE 
		BEGIN
		ROLLBACK TRAN
		Return 0
		END
GO

EXEC myDangerousSproc master database
GO


I did this as a joke, but then realized I should probably put the rollbacks in because someone might actually run it...



Brett

8-)
Go to Top of Page

Andraax
Aged Yak Warrior

790 Posts
Posted - 2003-07-02 : 16:55:55
Sorry to spoil your fun Brett... But you cannot drop the master database or any other system database... You just get an error message... :)
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2003-07-02 : 17:03:33
You get the point....this was a fun thread....



Brett

8-)
Go to Top of Page

Andraax
Aged Yak Warrior

790 Posts
Posted - 2003-07-03 : 02:07:10
As a matter of fact, this code from above:

AND xtype = 'U'

is quite worthless. You cannot drop system tables either.
Go to Top of Page

akki
Starting Member

14 Posts
Posted - 2003-07-08 : 05:22:16
After getting two replys to my topic, I thought I got required answer and didn't looked back. Just now I have seen the rest.

So I shouldn't use my little procedure (you know... its my first proccedure)

mhhhhh... let me explain why I am using that.

I will be creating few Dummy tables during the execution of some queries and after completing required operations I will be dropping those tables.

I have changed the procedue and added the following condition

IF EXISTS(SELECT name FROM sysobjects WHERE name like 'Dummy%' and name = @TName)

as all my dummy tables start with "Dummy"

Hope this will be fine.

Thank you all for your help

--akki.


Go to Top of Page

mr_mist
Grunnio

1870 Posts
Posted - 2003-07-08 : 06:44:10
You would still, in general, I believe, be better off doing proper

DROP TABLE dummy1
DROP TABLE dummy2

etc.

In your procedure rather than relying on the dynamic SQL to save you typing a few lines of code.

I think that it's much better if you can to avoid a dynamic SQL solution where there is another solution that does not use it. You will avoid headaches later on if you start best practices now.

Dynamic SQL will throw your permission schemes out the window.

-------
Moo.
Go to Top of Page

akki
Starting Member

14 Posts
Posted - 2003-07-08 : 08:17:54
My program goes like this

select * into Dummy1 from SomeTable1 where f1 > 1000 and f2 < 2000
select * into Dummy2 from SomeTable1 where f2 > 1000 and f2 < 2000
.
.
.
.
select * into Dummy100 from SomeTable1 where f4 > 1000 and f2 < 2000

this way we have few 100s of queries getting from 5 servers across the network. Suppose some query failed in between due to some network failure or so, it writes corresponding message to a log file and exits the system.

at this point I am not sure how many dummy tables are created. If I try to drop a table say Dummy50, which is not craeted, I will get a run time error. I hate using "On Error Resume Next" like statements in VB.

Finally another question, is checking in system tables the only option to know the existence of any object? or is there any other way?

Thanks a lot for your interest.

--akki.
Go to Top of Page

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts
Posted - 2003-07-08 : 09:06:50
you could use TEMPORARY tables.....

tables prefaced by # (or ##) depending on the scope of the required tables.


do a "forum search" for "temporary table" or "#" and follow the links....they might be what you want....

the good point about them is that they die after use....
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site