dynnamic proc to create tables

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)

dynnamic proc to create tables

Author

Topic

jaskalirai
Starting Member

31 Posts

Posted - 2007-12-13 : 14:20:59

declare
@TableName varchar(50),
@SqlCode varchar(max),
@columnOrder varchar (50),
@ColumnName varchar(50),
@DataType varchar(50),
@length varchar(50),
@Precision Varchar (50),
@Collation varchar(50),
@Nullable varchar (50),
@MaxColumnId int

select @tablename = 'TestTable'
select @maxcolumnid = max(columnorder)
from code where tablename = @tablename
declare tabledetails cursor for
Select
-- [TableName],
[ColumnOrder],
[ColumnName],
[DataType],
isnull([Length],''),
[Precision],
[Collation],
case [Nullable] when 'yes' then 'Null' else 'not null' end
from code
order by columnorder
open tabledetails
fetch next from tabledetails into @columnOrder, @columnName, @Datatype, @Length, @precision, @collation, @nullable
while @@fetch_Status = 0
Begin
If @ColumnOrder = 1
Begin
Select @sqlCode = 'CREATE TABLE [dbo].['
+ @TableName + '] ( ['
+ @ColumnName + '] ['
+ @DataType + '] ('
+ case when @Length = '' then + ' ' + @length + ' ' else ' (' + @length + ')' end
+ case when @precision is null then ') ' else ',' + @precision + ') ' end
+ @Nullable
+ ','
End
If @columnorder <> 1 and @columnOrder <> @Maxcolumnid
Begin
Select @sqlCode = @SqlCode + ' [' + @ColumnName + '] [' + @Datatype + ']'
if @length <> ''
BEGIN
SELECT @sqlcode = @sqlcode + ' (' + @length + ') ' + @nullable + ','
END
if @length = ''
BEGIN
SELECT @sqlcode = @sqlcode + ' ' + @length + ' ' + @nullable + ','
END
--select @columnOrder, @columnName, @Datatype, @Length, @precision, @collation, @nullable

End
If @columnorder = @Maxcolumnid
Begin

Select @sqlCode = @SqlCode + ' [' + @ColumnName + '] [' + @Datatype + '] (' + @length + ') ' + @nullable + ' ) ON [Primary] '
--select @columnOrder, @columnName, @Datatype, @Length, @precision, @collation, @nullable

End
fetch next from tabledetails into @columnOrder, @columnName, @Datatype, @Length, @precision, @collation, @nullable

End
Close tabledetails
Deallocate tabledetails

--select * from code where tablename = @tablename

select @sqlcode as sqlcode

Below is what sqlcode return when i execute the query

CREATE TABLE [dbo].[TestTable] ( [Column1] [int] ( ) not null, [Column2] [varchar] (7) not null, [Column3] [bit] not null, [Column4] [varchar] (55) Null, [Column5] [varchar] (12) Null, [Column6] [varchar] (24) Null, [Column7] [varchar] (56) Null ) ON [Primary]

what i am having trouble with

1. i want to get rid of the brackets where the datatype is not varchar >>>> [Column1] [int] ( )

2. i also want to add the precision and collation into the code so the code reads as follows

@columnOrder, @columnName, @Datatype, @Length, @precision, @collation, @nullable

I have tried everything i can think of including nested statements

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-12-13 : 21:14:16

I am not sure what you are trying to

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=92587
This seems like what you are trying to do..somethings I wrote a while back which generate the create table syntax for a table that already exists...maybe it will give you some ideas for how to tackle your problem.

Poor planning on your part does not constitute an emergency on my part.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-14 : 02:25:30

Why do you want to create tables dynamically?
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-12-14 : 04:00:16

This is a continuation of http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=94085

You are getting closer, you are nearly there with http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=94213

Juat take the brackets out of the collation part and you will have it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources