September 21, 2010 at 3:21 pm
I am writing a stored procedure to drop and then recreate a table. (don't ask, it was part of the spec)
Every time I try to execute CREATE PROCEDURE I get an error indicating that there is incorrect syntax near ';'. When I remove the semi-colon the error indicates incorrect syntax near TempML. Both errors occur on the DROP TABLE line.
Here is a condensed version of code:
CREATE PROCEDURE dbo.sp_ResetMarketingListTable
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
/****** Object: Table [dbo].[TempML] Script Date: 09/17/2010 14:36:45 ******/
IF EXISTS (SELECT *
FROM sys.objects
WHERE object_id = Object_id(N'[dbo].[TempML]')
AND TYPE IN ( N'U' ))
DROP TABLE [dbo].[TempML];
GO
/****** Object: Table [dbo].[TempML] Script Date: 09/17/2010 14:36:45 ******/
PRINT 'Creating table [dbo].[TempML].';
CREATE TABLE [dbo].[TempML]
(
...
)
ON [PRIMARY]
GO
END
I did some research and found that a DROP TABLE command in a stored procedure requires the schema name be included. As you can see, this is already present.
Does anyone know why I get this error? Based on my research so far, this should work fine. BTW, I even commented out the IF EXISTS part with the same results.
September 21, 2010 at 3:24 pm
Remove the GO in the middle of the procedure after DROP TABLE dbo.TempML;
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 21, 2010 at 3:26 pm
Try removing the "GO" from the procedure.
You cannot use Go instead a stored procedure.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
September 21, 2010 at 3:27 pm
The problem is not with the ;, it's the GO
GO is not a T-SQL command, it's a directive to the client tools that indicates where the batch ends. Hence, the batch, and stored procedure, ends at the statement GO. This results in errors because there's a BEGIN without an END in the first batch, and an END without a BEGIN in the last batch.
Your stored proc should read like this:
CREATE PROCEDURE dbo.sp_ResetMarketingListTable
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
/****** Object: Table [dbo].[TempML] Script Date: 09/17/2010 14:36:45 ******/
IF EXISTS (SELECT *
FROM sys.objects
WHERE object_id = Object_id(N'[dbo].[TempML]')
AND TYPE IN ( N'U' ))
DROP TABLE [dbo].[TempML];
/****** Object: Table [dbo].[TempML] Script Date: 09/17/2010 14:36:45 ******/
PRINT 'Creating table [dbo].[TempML].';
CREATE TABLE [dbo].[TempML]
(
...
)
ON [PRIMARY]
END
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 21, 2010 at 3:32 pm
Funny, I did remove it at one time but had other issues so put it back.
Removed it (along with a few others further down) and did some additional cleanup and it works.
Sometimes I look at things forever and never notice the obvious.
Thanks a ton!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply