Problem with Stored Proc Used to Refresh Table Data

[Cheryl3D]

Hi:

I have a stored procedure that I'm trying to create to drop an existing table then re-create the table, because each time a SELECT SQL statement is run on the table which is based on an INSERT INTO statement from a another table the data needs to be grouped accordingly. Here is my stored procedure that I'm trying to run (over and over) in SQL Server 2000:

CREATE PROCEDURE spRefreshMonths
AS

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Months]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Months]
GO

CREATE TABLE [dbo].[Months] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[user_id] [int] NOT NULL ,
[last_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[first_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Jan_bhrs] [numeric](18, 0) NULL ,
[Feb_bhrs] [numeric](18, 0) NULL ,
[Mar_bhrs] [numeric](18, 0) NULL ,
[Apr_bhrs] [numeric](18, 0) NULL ,
[May_bhrs] [numeric](18, 0) NULL ,
[Jun_bhrs] [numeric](18, 0) NULL ,
[Jul_bhrs] [numeric](18, 0) NULL ,
[Aug_bhrs] [numeric](18, 0) NULL ,
[Sep_bhrs] [numeric](18, 0) NULL ,
[Oct_bhrs] [numeric](18, 0) NULL ,
[Nov_bhrs] [numeric](18, 0) NULL ,
[Dec_bhrs] [numeric](18, 0) NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Months] WITH NOCHECK ADD
CONSTRAINT [PK_Months] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO

INSERT INTO Months([user_id], last_name, first_name,Jan_bhrs, Feb_bhrs, Mar_bhrs, Apr_bhrs, May_bhrs, Jun_bhrs, Jul_bhrs, Aug_bhrs, Sep_bhrs, Oct_bhrs, Nov_bhrs, Dec_bhrs)
select [user_id], last_name, first_name,
case when month(workdate) = 1 then sum(bhrs) else 0 end,
case when month(workdate) = 2 then sum(bhrs) else 0 end,
case when month(workdate) = 3 then sum(bhrs) else 0 end,
case when month(workdate) = 4 then sum(bhrs) else 0 end,
case when month(workdate) = 5 then sum(bhrs) else 0 end,
case when month(workdate) = 6 then sum(bhrs) else 0 end,
case when month(workdate) = 7 then sum(bhrs) else 0 end,
case when month(workdate) = 8 then sum(bhrs) else 0 end,
case when month(workdate) = 9 then sum(bhrs) else 0 end,
case when month(workdate) = 10 then sum(bhrs) else 0 end,
case when month(workdate) = 11 then sum(bhrs) else 0 end,
case when month(workdate) = 12 then sum(bhrs) else 0 end
from weeks
GROUP BY [user_id], last_name, first_name, month(workdate)
ORDER BY last_name ASC

GO

---------------
Now, I know that the Months table already exists, but I want to drop it, every time that this stored procedure (SP) is run, and then create it fresh based upon the result of the INSERT INTO which is based on a SELECT statement that is based on another table (weeks).

My INSERT INTO statement works great. But when I first go into Query Analyzer and run the SP, it works great, drops the existing table then creates the new table and fills it correctly.

But if I go into Query Analyzer and run it again with a statement like 'exec spRefreshMonths' I get errors stating:

Server: Msg 2714, Level 16, State 6, Line 2
There is already an object named 'Months' in the database.
Server: Msg 2714, Level 16, State 4, Line 2
There is already an object named 'PK_Months' in the database.
Server: Msg 1750, Level 16, State 1, Line 2
Could not create constraint. See previous errors.

Then when I go into the SP to look at the syntax (edit mode):

Then instead of my entire SP as denoted above, all I see in the syntax from spRefreshMonths is:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE spRefreshMonths
AS

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Months]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Months]

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

So the next time I run it all it does is drop the table, but it does not create it based on the INSERT INTO statement because now it is missing CREATE TABLE... as part of the SP syntax.

What am I doing wrong in my original SP that is causing this behavior?

Any help would be greatly appreciated.
Thanks,
Cheryl3D

 

[zhavic]

Do not use GO statement in stored procedure.

Also, you can create primary key in CREATE TABLE statement:

CREATE TABLE [dbo].[Months] (
    [id] [int] IDENTITY (1, 1) NOT NULL ,
    [user_id] [int] NOT NULL ,
    [last_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [first_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Jan_bhrs] [numeric](18, 0) NULL ,
    [Feb_bhrs] [numeric](18, 0) NULL ,
    [Mar_bhrs] [numeric](18, 0) NULL ,
    [Apr_bhrs] [numeric](18, 0) NULL ,
    [May_bhrs] [numeric](18, 0) NULL ,
    [Jun_bhrs] [numeric](18, 0) NULL ,
    [Jul_bhrs] [numeric](18, 0) NULL ,
    [Aug_bhrs] [numeric](18, 0) NULL ,
    [Sep_bhrs] [numeric](18, 0) NULL ,
    [Oct_bhrs] [numeric](18, 0) NULL ,
    [Nov_bhrs] [numeric](18, 0) NULL ,
    [Dec_bhrs] [numeric](18, 0) NULL ,
    CONSTRAINT [PK_Months] PRIMARY KEY  CLUSTERED
    (
        [id]
    )  ON [PRIMARY]

) ON [PRIMARY]

Also, you can simply delete all rows from table Months instead of recreating it:

DELETE FROM dbo.Months


Zhavic



---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.