sql server programming: Create table using procedure. -- sql server programming

Re: Create table using procedure.

venus
12/4/2006 1:17:08 AM

Thanks a lot....

[quoted text, click to view]

Re: Create table using procedure.

Tom Cooper
12/4/2006 2:39:28 AM

sp_executesql can only take one variable or constant as its statement. You
can't concatenate things like
N'CREATE TABLE' @plus '(t1 int)'
in the sp_executesql, you have to first concatenate them all in one
variable. Also, when you build the statement, you need a space after CREATE
TABLE and before the name of the table. And since you commented out the
incrementing of @a, you have an infinite loop. Finally, you must set the
value of @plus inside the loop, otherwise you are trying to create the same
table over again. So something like:

DECLARE @a INT, @sql nvarchar(255), @Tabname varchar(20), @plus char(20)
SET @a = 1
SET @Tabname = 'TestingLoad'
WHILE @a < 10
BEGIN
SET @plus = (@Tabname + cast (@a as varchar(20)))
SET @sql = N'CREATE TABLE ' + @plus + '(t1 int)'
EXEC sp_executesql @sql
SET @a = @a + 1
END
PRINT 'All Done'

Tom

[quoted text, click to view]

Re: Create table using procedure.

Uri Dimant
12/4/2006 9:28:09 AM

venus

DECLARE @a INT, @sql Nvarchar(255), @Tabname SYSNAME, @plus char(20)
SET @a = 1
SET @Tabname = 'TestingLoad'
set @sql = 'CREATE TABLE '+@Tabname+' (t1 int)'
exec sp_executesql @sql

WHILE @a < 10
BEGIN
set @sql = 'ALTER TABLE '+@Tabname+' ADD COL_'+CAST(@a AS VARCHAR(10))+ '
VARCHAR(50)'
-- PRINT @SQL
EXEC sp_executesql @sql
SET @a = @a + 1
END
PRINT 'All Done'

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.