Help to create new table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help to create new table

Author

Topic

lakon15
Starting Member

12 Posts

Posted - 2007-12-25 : 23:15:55

Dear All
I'm very new bie in MS SQL server
I want to know is it possible to create new table with, table name and column field as parameter, the column fields may be more than 4, all data type is real.
If possible is there any example to do that ?
Thanks before

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-25 : 23:18:25

Do you have the Books OnLine ? http://msdn2.microsoft.com/en-us/library/ms174979.aspx


create table <table name>
(
    <column name>   <data type>,
    <column name>   <data type>
)

KH
[spoiler]Time is always against us[/spoiler]

lakon15
Starting Member

12 Posts

Posted - 2007-12-25 : 23:36:52

No, that's not what I need !.
I need to make store procedure to create new table with table name and column name as parameter !, Let say if some one want to create table from application he only parsing table name, column2 names and column index.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-26 : 00:04:02

You can do that only by means of dynamic sql:-

This assumes you have two parameters @TableName specifying the table
and @ColumnNames giving comma seperated list of column names for table.

CREATE PROC TableCreationSP
@TableName varchar(100),
@ColumnNames varchar(8000)
AS
DECLARE @Sql  varchar(8000),@ColumnName varchar(100)

SET @ColumnName = CASE WHEN CHARINDEX(',',@ColumnNames) >0
                       THEN LEFT(@ColumnNames,CHARINDEX(',',@ColumnNames)-1)
                       ELSE @ColumnNames
                   END
SET @ColumnNames =CASE WHEN CHARINDEX(',',@ColumnNames) >0
                       THEN RIGHT(@ColumnNames,CHARINDEX(',',@ColumnNames)+1)
                       ELSE NULL
                  END
SET @Sql= 'CREATE TABLE ' + @TableName + ' (' + @ColumnName + ' real)'

EXEC (@Sql)

WHILE @ColumnNames IS NOT NULL
BEGIN
SET @ColumnName = CASE WHEN CHARINDEX(',',@ColumnNames) >0
                       THEN LEFT(@ColumnNames,CHARINDEX(',',@ColumnNames)-1)
                       ELSE @ColumnNames
                   END
SET @ColumnNames =CASE WHEN CHARINDEX(',',@ColumnNames) >0
                       THEN RIGHT(@ColumnNames,CHARINDEX(',',@ColumnNames)+1)
                       ELSE NULL
                  END
SET @Sql= 'ALTER TABLE '+ @TableName + ' ADD COLUMN '+ @ColumnName + ' real'

EXEC (@Sql)

END
GO

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-26 : 01:13:36

quote:
Originally posted by lakon15

No, that's not what I need !.
I need to make store procedure to create new table with table name and column name as parameter !, Let say if some one want to create table from application he only parsing table name, column2 names and column index.

Why do you let the users create table dynamically?
Make sure to read this
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

dev2dev
Starting Member

48 Posts

Posted - 2007-12-26 : 01:19:38

I was using ADOX in past to scan/create/alter tables from my vb applications

lakon15
Starting Member

12 Posts

Posted - 2007-12-26 : 02:09:30

This is what I'm doin right now,
this Sp is working but I need SP more flexible, because some time user can makes new table with huge column (more than 40 columns).

Why I let user create table, because only user knows the data and the schema of tables, we only maintai daa who sent by users !

Create procedure [dbo].[spCreateTable]
---@tabname varchar(32),
@uidPath varchar(325),
@columnIndex varchar(32),
@colname1 varchar(32) = null,
@colname2 varchar(32) = null,
@colname3 varchar(32) = null

with recompile
as
begin
Declare @pathHash bigint,
@query varchar(1000),
@comma varchar(10)

-- convert path to hash
Set @pathHash= [dbo].fnFNVHash(@uidPath)
set @comma = ','

set nocount on

/* check reserved name isn't in use */
if exists (select * from sysobjects where name = 'WMLogData_'+cast(@pathHash as varchar(10)))
begin
print 'Table WMLogData_'+cast(@pathHash as varchar(10))+' already exists.'
return(1)
end

/* check table name doesn't exist yet */
if exists (select * from sysobjects where name = 'WMLogData_'+cast(@pathHash as varchar(10)))
begin
print 'Table already exists WMLogData_'+cast(@pathHash as varchar(10))
return(1)
end

/* create table with just a dummy column as a start */
set @query='create table WMLogData_'+cast(@pathHash as varchar(10)) + '('

if (@colname1 is not null)
set @query = @query + @colname1 +' Real '+@comma

if (@colname2 is not null)
set @query = @query + @colname2 +' Real '+@comma

if (@colname3 is not null)
set @query = @query + @colname3 +' Real '+@comma

else
Set @query = @query + ')
CREATE CLUSTERED INDEX [IX_WMLogData_'+cast(@pathHash as Varchar(64))+']
ON [WMLogData_'+cast(@pathHash as Varchar(64))+'] ( ['+@columnIndex+'] ASC )'

exec(@query)

end

Go

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources