Creating tables in stored procedure called using ADO

[Chill]

I'm using VB6, SQL 7, and ADO... what's the best method to create a table (actually it will be several tables) with a name specified in a VB variable?

I was guessing using a stored procedure called using ADO with one parameter. Ideally I'd like the stored procedure to be something like.

sp_Create_Location_Tables @loc as nvarchar(10)

Will this work? I'm having problems where the stored procedure does something like:

CREATE TABLE [dbo].[@loc] and the name of the table actually ends up being "@loc" not the value of the parameter. Any ideas on how to do this? Also is there a way to append a string in the stored procdure, in the end I want the sp to create 3 different tables each being (using VB syntax) something like @loc & "_vendor", @loc & "_product", etc..

Thanks!

------------------

[VorTechS]

Is there any reason why you are using a stored procedure to do it, and not firing it as a query from your application?

Altho I'm not up on my ADO, using a connection object the VB syntax would be: -

conMyconnection.execute("CREATE TABLE " & strTableName & strMyFieldsStatement)

As far as I am aware there is no significant difference in the time taken for SQL server to execute a VB prepared query, not seeing as you only intend to create 3 tables anyway.

[Chill]

The reason for wanting it in a stored procedure is that my table structure is still somewhat dynamic... and will probably continue to be so for awhile after the app development ends. For this reason all db access (queries, views, etc.) have been kept in the database. I'm fairly certain I can do it from code as well... but it would certainly result in less flexiblity later - and with the functionality of T-SQL I figure there must be a way to do it. I'm continuing to search for any answers...

Thank you,


------------------