proc to create table using dynamic sql

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)

proc to create table using dynamic sql

Author

Topic

jaskalirai
Starting Member

31 Posts

Posted - 2007-12-12 : 04:43:56

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
+ '] ('
+ @Length
+ ') '
+ @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 @sqlcode

Ok what i want to do is write a create table dynamically within a stored procedure. I am having trouble with precision AND collation variables.

for example precision and collation can both be null how would i manipulate the above the code. I have tried using the same if statement i used for length and collation but it doesnt work.

any ideas plz

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-12 : 04:51:28

Why do you want to create tables dynamically?

Madhivanan

Failing to plan is Planning to fail

jaskalirai
Starting Member

31 Posts

Posted - 2007-12-12 : 04:56:42

I am looking to create a ssis package which will maniuplate the data. so for example if have the tablename, columnorder, columnname, length, precision, collation, nullable in a excel spreadsheet with data in what i want to do is run the proc and then it shud automatically produce the code for me .

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-12-12 : 04:59:52

Use case statements to check if null and add accordingly.


Select @sqlCode = 'CREATE TABLE [dbo].[' 
+ @TableName + ']([' + @ColumnName + '] [' + @DataType + '] (' + @Length + case when @precision is null then ') ' else ',' + @precision +  ') ' end + @Nullable + ','
End

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-12 : 05:59:44

www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

jaskalirai
Starting Member

31 Posts

Posted - 2007-12-12 : 06:08:25

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
+ '] ('
+ @Length
+ ') '
+ @Nullable
+ ','

End

Begin
Select @sqlCode = @sqlcode
+ @TableName + ']([' + @ColumnName + '] [' + @DataType + '] (' + @Length + case when @precision is null then ') ' else ',' + @precision + ') ' end + @Nullable + ','
End

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

End

If @columnorder = @Maxcolumnid
Begin

Select @sqlCode = @SqlCode + ' [' + @ColumnName + '] [' + @DataType + '] (' + @Length + case when @precision is null then ') ' else ',' + @precision + ') ' end + @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 @sqlcode

is this what you mean ?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-12-12 : 06:54:55

You would need to put it on all the parts you use to create the columns.

jaskalirai
Starting Member

31 Posts

Posted - 2007-12-12 : 15:33:29

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 + '] (' + @Length + case when @precision is null then ') ' else ',' + @precision + ') ' end + @Nullable + ','
End

If @columnorder <> 1 and @columnOrder <> @Maxcolumnid
Begin

Select @sqlCode + '[' + @ColumnName + '] [' + @DataType + '] (' + @Length + case when @precision is null then ') ' else ',' + @precision + ') ' end + @Nullable + ','
End
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 + case when @precision is null then ') ' else ',' + @precision + ') ' end + @Nullable + ' ) ON [Primary] '

End
--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 @sqlcode

--select * from code where tablename = @tablename

have made the changes still not working any ideas ?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-12-13 : 03:32:06

Give us a clue. What error/output are you getting?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources