sql server 2000 dropping tables recieved as parameters 4

[duchovnick]

Hi,
I need a stored procedure to recieve 2 table names as parameters and to delete those 2 tables.
My code is:

/*drop proc deleteTables*/
/*exec deleteTables j_11, j_12*/
CREATE PROCEDURE deleteTables @table1 VARCHAR(10), @table2 VARCHAR(10)
AS
BEGIN TRANSACTION
declare @xxx varchar(200)
set @xxx=
'
IF EXISTS (SELECT * FROM sysobjects WHERE name='+@table1+')
  DROP TABLE '+@table1+'

IF EXISTS (SELECT * FROM sysobjects WHERE name='+@table2+')
  DROP TABLE '+@table2+'
'
commit
go

After execution of the procedure, the tables remain alive.
I guess the parameters were not found in "sysobjects".
Can anyone tell me how to do it right ?
Thanks a lot.

 

[AlexCuse]

I am not sure you can use dynamic SQL with a drop table command, but if you could you'd want to do it like this:

IF EXISTS (SELECT * FROM sysobjects WHERE name=''' + @table1 + '''')
DROP TABLE '''' + @table2 +''''

The way you have it set up, SQL was actually looking for a table called +@table1+

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[SQLDenis]

you have to use dynamic SQL however that is not very smart, what if someone passes in a table that you are not supposed to delete?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[bborissov]

CREATE PROCEDURE deleteTables @table1 VARCHAR(10), @table2 VARCHAR(10)
AS
BEGIN TRANSACTION
declare @xxx varchar(200)
set @xxx=
'
IF EXISTS (SELECT * FROM sysobjects WHERE name='+@table1+')
  DROP TABLE '+@table1+'

IF EXISTS (SELECT * FROM sysobjects WHERE name='+@table2+')
  DROP TABLE '+@table2+'
'

[COLOR=red]
[b]
EXEC (@xxx)
[/b]
[/color]
commit

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[AlexCuse]

can't believe I missed the missing exec...

Ignorance of certain subjects is a great part of wisdom

 

[duchovnick]

Hi ,
I repaired the code as suggested above and now it looks:

/*exec deleteTables j_11, j_12*/  
/*drop proc deleteTables*/
CREATE PROCEDURE deleteTables @table1 VARCHAR(10), @table2 VARCHAR(10)
AS
BEGIN TRANSACTION
declare @xxx varchar(200)
set @xxx=
'
IF EXISTS (SELECT * FROM sysobjects WHERE name=''+@table1+'')
  DROP TABLE '+@table1+'

IF EXISTS (SELECT * FROM sysobjects WHERE name=''+@table2+'')
  DROP TABLE '+@table2+'
'



EXEC (@xxx)


commit
go

but the result is the same. Tables j_11, j_12 still exist.
Thanks and i'd love to hear more suggestion.

 

[duchovnick]

you have to use dynamic SQL however that is not very smart, what if someone passes in a table that you are not supposed to delete?

Hi SQLDenis. Do you know other way to do it ? I'd love to hear.
Thanks !

 

[AlexCuse]

You are missing an apostrophe in your if exists portions:

/*exec deleteTables j_11, j_12*/  
/*drop proc deleteTables*/
CREATE PROCEDURE deleteTables @table1 VARCHAR(10), @table2 VARCHAR(10)
AS
BEGIN TRANSACTION
declare @xxx varchar(200)
set @xxx=
'
IF EXISTS (SELECT * FROM sysobjects WHERE name='[COLOR=red]'[/color]'+@table1+'[COLOR=red]'[/color]')
  DROP TABLE '+@table1+'

IF EXISTS (SELECT * FROM sysobjects WHERE name='[COLOR=red]'[/color]'+@table2+'[COLOR=red]'[/color]')
  DROP TABLE '+@table2+'
'



EXEC (@xxx)


commit
go

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[bborissov]

Alex,
and I missed absence of the single quote

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[AlexCuse]

Boris - between the two of us I think we got it ;-)

Duchovnick - why do you need to have users drop tables anyway? I'd think you can automate it when it is needed in your process. Even with drop downs controlling the parameters passed in, this sounds like risky business (whatever you give users the opportunity to mess up, they will ;-) )



Ignorance of certain subjects is a great part of wisdom

 

[duchovnick]

Hi !
It worked just fine and i'm gratefull to you Alex and Borislav.

 

[gmmastros]

As Denis says... It's not a good idea to do things this way. With the code provided, any use could use this procedure to drop any table. Instead, you could code things this way...

CREATE PROCEDURE deleteTables @table1 VARCHAR(10), @table2 VARCHAR(10)
AS
BEGIN TRANSACTION

If @Table1 = 'TableToDelete' Or @Table2 = 'TableToDelete'
  Begin
    IF EXISTS (SELECT * FROM sysobjects WHERE name='TableToDelete')
      Begin    
        DROP TABLE TableToDelete
      End
  End

If @Table1 = 'AnotherTable' Or @Table1 = 'AnotherTable'
  Begin
    IF EXISTS (SELECT * FROM sysobjects WHERE name='AnotherTable')
      Begin    
        DROP TABLE AnotherTable
      End
  End

With the code written this way, ONLY certain tables (hardcoded in the stored procedure) can be dropped. Of course, this will require more coding, but it is a lot safer to do things this way. The last thing your application needs is for an unexpected table to get dropped accidentally.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

if you need to create and drop tables why not use temporary tables instead?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[duchovnick]

Hi SQLDenis,gmmastros
Temporary table means views ? Otherwise i dont know what temporary tables mean.
Where i work i'm the only person with access to the database and what matters here is doing things fast not safe (luckily i found this forum to solve problems fast because i still am a beginner and i'm required to respond quickly to problems. Thats why i try to create generic procedures to help me respond fast to random requirements.
Thanks a lot for your help and the code attached.

 

[SQLDenis]

a temp table is a table that will be dropped once the connection/code/proc is finished

example

create table #wasabi(id int) --notice the pound sign

insert #wasabi values (1)

select * from #wasabi

now close the window open another window and execute this again

select * from #wasabi

you will get this error

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name '#wasabi'.


because a (loca) temp table only exists for its connection (scope)


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[duchovnick]

Thanks a lot
SQLDenis

 

[duchovnick]

Hi gmmastros,
Sorry to have forgotten thanking you too ...

 

[SQLWilts]

Just one thought - you could use a table variable too if you are in SQL 2000

Declare tbl1 Table (
                tblID    Int Not Null
                ,field1  varchar(32) not null
                   )

etc etc

then you wouldn't even touch the temp table. Just a thought