Create new table with variable passed to stored procedure from vb

[indydavid32]

Hi everyone. I want to execute a stored procedure from my vb code with a string variable, and create a new table in my database and name the table based on that variable.

I tried the following, but the table was named EFS_@dteYear

Create procedure [dbo].[EFS_Table_Creator]
@dteYear char(4)
as
Create Table dbo.EFS_@dteYear (
pkey char(62) Null,
location char(7) null)
GO

I called this stored procedure like this.

rs.Open "Exec EFS_Table_Creator '2004'"

Anyone know what I did wrong?

Thanks

[techgnome]

Simply put, it doesn't work like that.

What you'll need to do is build your SQL dynamically into a variable, then EXECute the string. It's the only way.

TG

[indydavid32]

Would you give me an example please?

Thanks

[szlamany]

Be sure that you want to actually do this - creating a table for each year is not always the best way to archive.

Archiving data can be done by simply having an archive table that exactly matches the table you want to archive, but add one column to the beginning - in your case ArchiveYear.

Then you would simply archive the data by:

Insert into ArchiveTable Select @dteYear,* From SourceTable

[indydavid32]

All I want is an example on how to programmatically create SQL 2000 database tables using VB.

Thanks

[vb_dba]

This is what techgnome is talking about (I think):

Create procedure [dbo].[EFS_Table_Creator]
@dteYear char(4)
as
Declare @sql varchar(1000)

Set @Sql = 'Create Table dbo.EFS_' + @dteYear + ' (pkey char (62) Null, location char(7) null)'

Exec (@Sql)

[indydavid32]

Thanks a lot vb_dba. That works perfectly. Just what I needed.