create Dynamic table in SQL 2000??!!!

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 2000 Forums

SQL Server Development (2000)

create Dynamic table in SQL 2000??!!!

Author

Topic

zokho
Starting Member

10 Posts

Posted - 2005-06-07 : 04:57:25

hi
ive a question about dynamic table in sql 2000.
how can i create a procedure which user can send into that a table's name and fieldsName and that proc builds that table?

for example:
Exec Myproc 'tableName' 'field1,field2,field3,...'

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-06-07 : 05:05:35

You would have to use dynamic sql to do this.
see sp_executesql or EXECUTE().

Why would You want to do this anyway ?
This would come high on the "bad practice list".

rockmoose

zokho
Starting Member

10 Posts

Posted - 2005-06-07 : 18:47:34

i did it my self and i wright its code for any body who want to get help of that for his/her problem:
and thank u rockmoose for visiting my question...
this is the code:
CREATE PROC MAKE_TABLE
(
@TABLENAME VARCHAR(15),
@FIELDSNAME VARCHAR(1000)
)
AS
BEGIN
DECLARE @FIELD VARCHAR(30),@POS INT,@POS1 INT,@EXTRA varchar(1000),@STRINGTABLE VARCHAR(1000),@S2 NVARCHAR(1000)
SET @EXTRA=@FIELDSNAME
SET @POS=CHARINDEX(',',@EXTRA)
SET @POS1=0
SET @STRINGTABLE='CREATE TABLE '+@TABLENAME+'('
WHILE @POS1<>LEN(@FIELDSNAME)
BEGIN
SET @FIELD=SUBSTRING(@EXTRA,0,@POS)
SET @STRINGTABLE=@STRINGTABLE+@FIELD+','
SET @EXTRA=SUBSTRING(@EXTRA,@POS+1,LEN(@EXTRA))
SET @POS1=@POS1+@POS
SET @POS=CHARINDEX(',',@EXTRA)
END
SET @STRINGTABLE=@STRINGTABLE+')'
SELECT @S2=CAST(@STRINGTABLE AS NVARCHAR(1000))
EXECUTE SP_EXECUTESQL @S2
END

and execute that by this way:
Exec MakeTable 'tableName' 'field1 datatype,field2 datatype,...,'
"Dont forget to put a ',' in the last of fieldn data type as i did"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources