Drop & Create sProc

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,
MarcEncapsulate the CREATE / DROP statements within an IF ?
IF OBJECT_ID('[YourObject]','U') IS NOT NULL
BEGIN
DROP ....
CREATE ...
END
"Marc Miller" wrote:

> 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
>
>|||Marc Miller (mm1284@.hotmail.com) writes:
> 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?
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.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx