dynamic create 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

General SQL Server Forums

New to SQL Server Programming

dynamic create table .

Author

Topic

a4u6178
Starting Member

11 Posts

Posted - 2011-07-28 : 02:50:20

hello everyone,
I want to create sql tables dynamically by using the stored procedure,my stored procedure is like this
create procedure usp_demo (@TableName varchar(90))
as
declare @SQL varchar(1000)
SELECT @SQL = 'Create Table' + @TableName + '('
SELECT @SQL = @SQL + 'ID int NOT NULL Primary Key, FieldName VarChar(10))'
exec usp_demo @TableName='cus'
this executes , but when i try see the creates table it gives error..
Invalid object name 'cus'.

Thanks & Regards,

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-07-28 : 03:34:49

Try this -


ALTER PROCEDURE usp_demo (@TableName varchar(90))
as
BEGIN

declare @SQL nvarchar(1000)
SELECT @SQL = 'Create Table ' + @TableName + ' ('
SELECT @SQL = @SQL + 'ID int NOT NULL Primary Key, FieldName VarChar(10))'
SET @SQL = @SQL 

EXECUTE SP_EXECUTESQL @SQL

END

exec usp_demo 'cus'

Vaibhav T

If I cant go back, I want to go fast...

michael.appleton
Posting Yak Master

160 Posts

Posted - 2011-07-28 : 04:41:28

This is a bad idea. There's really no need to create a table dynamically like this. If you really need to use dynamic SQL in this way though, use the function QUOTENAME() on any table or column name parameters to stop any SQL injection (whether deliberate or a typo) from doing anything unwanted.

a4u6178
Starting Member

11 Posts

Posted - 2011-07-28 : 05:03:13

Hello vaibhavktiwari83,
I tried ur code its working fine, thank you...
one more thing i would like to ask is that the coloumns of table can also be added in similar way..

ALTER PROCEDURE usp_demo (@TableName varchar(90),@ID int,@FieldName varchar(90))
as
BEGIN

declare @SQL nvarchar(1000)
SELECT @SQL = 'Create Table ' + @TableName + ' ('
SELECT @SQL = @SQL +''+@ID+ 'int NOT NULL Primary Key,' +@FieldName+ 'VarChar(10))'
SET @SQL = @SQL

EXECUTE SP_EXECUTESQL @SQL

END
Looking forward for help....
exec usp_demo 'cus','CusID','CustName'

Thanks & Regards,

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-07-28 : 05:28:29

[code]
ALTER PROCEDURE usp_demo (@TableName varchar(90),@ID varchar(90),@FieldName varchar(90))
as
BEGIN

declare @SQL nvarchar(1000)
SELECT @SQL = 'Create Table ' + @TableName + ' ('
SELECT @SQL = @SQL +''+@ID+ ' int NOT NULL Primary Key,' +@FieldName+ ' VarChar(10))'
SET @SQL = @SQL

EXECUTE SP_EXECUTESQL @SQL

END

exec usp_demo 'cus','CusID','CustName'
[/code]

Note that column name cant be int datatype.

Vaibhav T

If I cant go back, I want to go fast...

a4u6178
Starting Member

11 Posts

Posted - 2011-07-28 : 06:59:05

Thank you very much for ur constant support...

Thanks & Regards,

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-07-28 : 07:05:43

You are welcome...

Vaibhav T

If I cant go back, I want to go fast...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources