July 29, 2008 at 1:54 pm
I am creating a Stored Procedure in SQL Server 2005 and I am getting this error message and I can't figure out why:
Msg 156, Level 15, State 1, Procedure QIDMonthlyCounts, Line 4
Incorrect syntax near the keyword 'IF'.
Here is the code:
USE [HCAHPS]
GO
CREATE PROCEDURE [dbo].[QIDMonthlyCounts]
IF Exists (SELECT * FROM sysobjects WHERE NAME = 'StmtMonthlyCounts')
DROP Table StmtMonthlyCounts
GO
CREATE TABLE dbo.StmtMonthlyCounts (
Factor INT NOT NULL,
CountofFactor MONEY NOT NULL,
QID VARCHAR(3) NOT NULL,
YR VARCHAR(4) NOT NULL,
MTH VARCHAR(2) NOT NULL
)
GO
Any help is appreciated.
Ron
July 29, 2008 at 2:00 pm
rconway (7/29/2008)
I am creating a Stored Procedure in SQL Server 2005 and I am getting this error message and I can't figure out why:Msg 156, Level 15, State 1, Procedure QIDMonthlyCounts, Line 4
Incorrect syntax near the keyword 'IF'.
Here is the code:
USE [HCAHPS]
GO
CREATE PROCEDURE [dbo].[QIDMonthlyCounts]
IF Exists (SELECT * FROM sysobjects WHERE NAME = 'StmtMonthlyCounts')
DROP Table StmtMonthlyCounts
GO
CREATE TABLE dbo.StmtMonthlyCounts (
Factor INT NOT NULL,
CountofFactor MONEY NOT NULL,
QID VARCHAR(3) NOT NULL,
YR VARCHAR(4) NOT NULL,
MTH VARCHAR(2) NOT NULL
)
GO
Any help is appreciated.
Ron
You are missing the AS statement after the CREATE PROCEDURE. It should be:
CREATE PROCEDURE procedure_name
AS
But, why do you need to drop/recreate the table this way? Really shouldn't be needed.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 29, 2008 at 2:25 pm
Sorry for the post!! Something so simple to overlook.
The reason I am deleting this table and recreating it is that the background data that is aggregated into this table changes nightly. I have multiple INSERT INTO statements in the script of the Stored Procedure that I didn't put into my post because that part is working fine.
I suppose I could truncate the table instead of drop/create. Is there any performance issue of one over the other?
Thanks for your help.
July 29, 2008 at 2:38 pm
I am not sure if there is really a performance issue - but, truncating the table is (IMHO) a bit cleaner. I do know that truncating the table will be logged (minimally, but still logged) - I am not sure whether or not dropping is logged at all though.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 30, 2008 at 12:18 am
Truncate Table command is logged but deallocation of pages is recorded, NOT the row level datya as in Delete command and Data is NOT recoverable after Commit.
In case of Delete command, all the records are logged that are deleted and the Data is recoverable even after commit.
But the OP is dropping table, so the Truncate table command will be suitable for him as it as much faster than Delete.
The Drop Table command is a DDL command and is logged as DDL. the data is NOT recorded in the Transaction Log. Within a
transaction, rolling back a DROP statement will retain all the data as it
is.
Atif Sheikh
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply