create tables with prefixes

[Guest_imported]

I wanted to create a table with a varying prefix but it generates this error:

Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '@ID_sample'.

Here's the T-SQL statement I used:

declare @ID varchar(10)
set @ID='samp'

create table @ID_sample (
sampID char(10)
)

This is just my prototype. I am supposed to do this in a stored procedure but I wanted to test it on query analyzer first. I hope you could help me.

 

[Tim1]

Hiya,

The problem with your statement is that so far as the SQL is concerned, you have declared one variable, called @ID, then assigned a variable. However, when you run your create statement, you use a variable called @ID_sample.

What you will need to do is:

DECLARE @ID VARCHAR(50)
SET @ID = 'samp'
SET @ID = @ID + '_sample'

CREATE TABLE @ID .....

HTH

Tim

 

[tlbroadbent]

THe Create Tabel Statement cannot accept a variable for the table name. Use the following syntax.

Declare @ID(int), @sql varchar(128)
Set @ID='samp'
Select @sql='create table ' + @ID + '_sample(IDsamp(int))'
Execute(@sql) Terry

http://members.home.net/tlbroadbent/index.htm

 

[Andel]

You can create a stored procedure that you can call "createtable" like this one...

CREATE PROC createtable (@tbl varchar(50))
AS
EXEC ('create table SAMP_' + @tbl + ' (SampleID Int)')

When you use this sp to create table, you'll get the prefix "SAMP_".

Example: CREATETABLE SAMPLE
You'll get "SAMP_SAMPLE"

Andel
andelbarroga@hotmail.com

http://andyb.8m.com