I generated SQL Script to drop and recreate some tables in my database
that I will want to perform monthly.  I would like to put the script into a
sproc, however when I try to compile it I receive an error that the tables
and indexes already exist in my database.

Is there any way around this other than dropping all the tables before I
compile
the sproc?

Thanks,
Marc

Encapsulate the CREATE / DROP statements within an IF ?

IF OBJECT_ID('[YourObject]','U') IS NOT NULL
BEGIN
  DROP .....
  CREATE ....
END

Show quoteHide quote

Marc Miller (mm1***@hotmail.com) writes:
Unless you are running SQL 6.5, you should not get that error.

I suspect that you have something that looks like:

   CREATE PROCEDURE yoursp AS

    CREATE TABLE abc
    CREATE INDEX abc_ix ON abc(def)
  go
    CREATE TABLE xyz
    CREATE INDEX xyz_ix ON xyz(www)
  go

This is a script that first creates a procedure, and then creates a table.
This is because the script includes "go" which is an instruction to
the query tool that this is where the batch ends. "go" is not an SQL
command.

Thus, you would have to remove the "go" to include all code in the
stored procedure. However, you may then find that run into other
problems, because you have commands that must be in separate batches.

I would suggest that you store the script as a separate file. This is
would you should with stored procedures as well. The database should
be seen as a binary repository.

NESTED SQL QUESTION and aggregate function
CLR Triggers and inserted table
SQL script, split digits and letters
Concatenating:www.aspfaq.com/show.asp?id=2529 Is the glass half full or half empty or..?
Query help - resultset too large... impossible!