Dropping tables dynamically in Stored Procedures
-
I am attempting to drop a table dynamically i.e. if it exists using the following code in a stored procedure.
if exists
(select *
from sysobjects
where id = object_id(N'[dbo].[table_1]')
and objectproperty(id,N'IsUserTable') = 1)
DROP TABLE table_1
The table currently exists so this code is suppose to check and drop the table as it does if I run this in Query Analyzer.
It doesn't work when put in a stored procedure is there any way to get this to work or will I have to drop all my tables at the end of my code to prepare for my next extraction.
-
If it works in QA, it'll work in the proc.. How are the codes different?
-
They are exactly the same I am using the code to create the proc. Give it a try...
-
Slightly different version but your version should work.
If it doesn't then you need to post the full sp code.
CREATE TABLE dbo.A (b INT NOT NULL) GO SELECT * FROM dbo.a IF EXISTS (SELECT * FROM dbo.sysobjects WHERE name = 'A' AND XType = 'U' AND USER_NAME(uid) = 'dbo') DROP TABLE dbo.A
-
Hi Ninja,
You gave me a clue to my issue when you said post the full sp code, I think my problem is in my use of go's i.e.
Create Proc Pchurn2
as
if exists
(select *
from sysobjects
where id = object_id(N'[dbo].[test_table]')
and objectproperty(id,N'IsUserTable') = 1)
DROP TABLE test_table
go
select top 5* into test_table from table1
when I remove the go it appears to work I am going to continue to test.
Thanks
-
That'll give you the answer you need recreate your proc with the code you just posted and see the result for yourself
.Select Text from dbo.SysComments where id = Object_id('Pchurn2')
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply