March 2, 2014 at 10:40 pm
Hi All
I have a procedure that tests if the table exists if so then drop it
What I am looking to do is if It exists then drop it, if it does not exist then just exit procedure
It is fine if it exists and drops it, but if it does not exist I get this error
Msg 3701, Level 11, State 5, Line 1
Cannot drop the table 'dbo.newtable', because it does not exist or you do not have permission.
Msg 3902, Level 16, State 1, Procedure droptable, Line 18
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
Here is my code
USE [TestData]
GO
/****** Object: StoredProcedure [dbo].[droptable] Script Date: 03/03/2014 15:24:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[droptable]
AS
Begin
declare @sqldrop nvarchar(1000)/* The drop table command */
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[newtable]') AND type in (N'U'))
BEGIN TRANSACTION InProc
/* Drop the table before creating it, just incase it already exists. */
set @sqldrop = 'DROP TABLE dbo.newtable' --+ quotename(@table_name)
exec (@sqldrop);
COMMIT TRANSACTION InProc;
--ELSE what do i do
end
where am I going wrong, thanks in advance
March 3, 2014 at 12:05 am
Too many statements for your IF...block without proper BEGIN...END
(hyphens added to get around my VPN's proxy, so remove those)
Try this:
IF EXISTS ( SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[newtable]') AND type IN ( N'U' ) )
BEGIN
BEGIN TRANSACTION InProc
/* Drop the table before creating it, just incase it already exists. */
SET @sqldrop = 'D-ROP TABLE dbo.newtable' --+ quotename(@table_name)
EXEC (@sqldrop);
COMMIT TRANSACTION InProc;
END
ELSE
BEGIN
PRINT 'You should do something here, like raise an error or something fancy'
END
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 4, 2014 at 8:37 am
I just do this:
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME ='newtable') DROP TABLE newtable;
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply