Hi Zili,

OK, maybe I was not so clear.  Here the code, cut and past, it should 
work:



CREATE PROCEDURE [dbo].[sp_CreateTables]
	@tableName varchar(20)
AS

BEGIN

DECLARE @strVerif varchar(500)
DECLARE @strSQL varchar(500)


SET @strSQL =3D 'IF EXISTS (SELECT name FROM sysobjects WHERE name=3D 
'''
SET @strSQL =3D @strSQL + @tableName
SET @strSQL =3D @strSQL + ''' AND type =3D ''U'') DROP  TABLE ' + 
@tableName

EXEC (@strSQL)

SET @strSQL =3D 'create table ' + @tableName + '(cityID int not null '
SET @strSQL =3D @strSQL + 'primary key, cityName varchar(25) )'

EXEC (@strSQL)

END
GO



-------------------------------------------------
Francois Vallieres
Observatoire des Sciences et des Technologies




-----Original Message-----
From: Zili Liu [mailto:liu@Q...]
Sent: 31 ao=FBt, 2001 15:03
To: sql language
Subject: [sql_language] RE: Store Proc with table name


Hi Francois,
I did everything that you mentioned. But I still cann't see 
sp_createTables
in the stored procedure folder.
I belive that your SQL statements are right. However, my purpose is to 
make
the stored procedure work.
Zili

-----Original Message-----
From: Francois_Vallieres@I...
[mailto:Francois_Vallieres@I...]
Sent: Friday, August 31, 2001 12:00 PM
To: sql language
Subject: [sql_language] RE: Store Proc with table name


Hi Zili,

1. Don't declare it, I just made a bad cut and past.  You should remove 
the
second @strSQL and the @tablename declaration should not be there, just 
in
your parameter (for my testing purpose, I declare it because I haven't 
made
a procedure)

2. If you don't see it, you should refresh if you successfully made the
store proc.  Verifiy also that you are in the good database.  Or don't
hesitate, use the syntax YouDataBase.dbo.sp_CreateTables


-------------------------------------------------
Francois Vallieres
Observatoire des Sciences et des Technologies



-----Original Message-----
From: Zili Liu [mailto:liu@Q...]
Sent: 31 ao=FBt, 2001 14:26
To: sql language
Subject: [sql_language] RE: Store Proc with table name


Hi Francois,

Thanks for your solution!
However I have 2 quetions for your solution:

1. why do you declare @strSQL and @tableName twice? ( I got compiler 
error
for this )
2. Did you see sp_CreateTables in the stored procedure folder.

Sorry for keeping asking quetions!

zili

-----Original Message-----
From: Francois_Vallieres@I...
[mailto:Francois_Vallieres@I...]
Sent: Thursday, August 30, 2001 4:16 PM
To: sql language
Subject: [sql_language] Store Proc with table name



Hi Zili,

Here the code (I try it and it work, so give me news about it) :

IF EXISTS (SELECT name
	   FROM   sysobjects
	   WHERE  name =3D N'sp_CreateTables'
	   AND 	  type =3D 'P')
    DROP PROCEDURE sp_CreateTables
GO

CREATE PROCEDURE sp_CreateTables
	@tableName varchar(6)
AS

BEGIN

IF EXISTS(SELECT name
	  FROM 	 sysobjects
	  WHERE  name =3D N'@t...'
	  AND 	 type =3D 'U')
    DROP TABLE @tableName
GO

DECLARE @strSQL varchar(500)


DECLARE @strSQL varchar(500)
DECLARE @tableName varchar(100)


SET @strSQL =3D 'IF EXISTS (SELECT name FROM sysobjects WHERE name '''
SET @strSQL =3D @strSQL + @tableName
SET @strSQL =3D @strSQL + ''' AND type =3D ''U'') DROP  TABLE ' + 
@tableName

EXEC (@strSQL)

SET @strSQL =3D 'create table ' + @tableName + '(cityID int not null '
SET @strSQL =3D @strSQL + 'primary key, cityName varchar(25) )'

EXEC (@strSQL)

END


-------------------------------------------------
Francois Vallieres
Observatoire des Sciences et des Technologies