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

SQL Server 2000 Forums

SQL Server Development (2000)

create a dynamic table???!!!

Author

Topic

zokho
Starting Member

10 Posts

Posted - 2005-06-09 : 09:39:13

hi i ve asked this question before and i solved it my self but now i want to solve that by another way????
AT first i ask my question again:
i wanna make a procedure which accept a table's name and fields names and then builds that table for example :
exec myproc 'tableName','field1 int,field2 varchar(10),...'
****Attention:i dont wanna use any of system proc to do that like:sp_executesql,...****

i put my last way to solve that here:
thank u for your helping...

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

EXEC MAKE_TABLE 'MYTABLE','FIELD1 INT,FIELD2 VARCHAR(10),'

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-09 : 09:45:06

what is wrong with your solution... why do you want to solve it a different way?

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.

zokho
Starting Member

10 Posts

Posted - 2005-06-09 : 10:10:10

because i did it for someone and he didnt accept it by this way

please help me if you can

]

quote:
Originally posted by Seventhnight

what is wrong with your solution... why do you want to solve it a different way?

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-09 : 10:22:02

did he give a reason for not accepting... or any indication of what way he wanted to see it done? There are always plenty of ways to skin a cat.

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-09 : 10:22:18

Does he want you to do it correctly and just supply DDL?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-06-09 : 12:56:57

It is my guess that he is writing his own Query Analyzer.

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-06-09 : 14:38:37

quote:
because i did it for someone and he didnt accept it by this way

Would this someone be your professor/teacher?

Dynamically creating a table without dynamic SQL is about as likely as swimming without getting wet.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-06-09 : 22:38:44

Tell the guy who didn't accept it that he a stupid IDIOT and be done with it. If he needs some clarification, give him my email address. I'll help him out.

Always happy to help people.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

zokho
Starting Member

10 Posts

Posted - 2005-06-10 : 05:04:17

hi guy thank you for your helping again and i didnt know your meaning about your suggestion ???!!
"Does he want you to do it correctly and just supply DDL?"
what do you mean by DDL?

quote:
Originally posted by X002548

Does he want you to do it correctly and just supply DDL?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

zokho
Starting Member

10 Posts

Posted - 2005-06-10 : 05:07:52

hi my dear yes that guy is my professor.
know what??
can you help me or not???
if not....?!!

any way thankyou for reading it

quote:
Originally posted by robvolk

quote:
because i did it for someone and he didnt accept it by this way
Would this someone be your professor/teacher?

Dynamically creating a table without dynamic SQL is about as likely as swimming without getting wet.

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-06-10 : 05:11:09

sweet mamma
did you click on the link?
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

You need to buy a good book on sql (I recommend "Inside SQL Server") and then come back to this site and ask any additional questions. trust me its the best thing for you. you will learn much quicker that way. there is a lot under the hood that you obviously dont know that you need to learn first otherwise you are just walking around in the dark.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-06-10 : 06:26:20

quote:
hi my dear yes that guy is my professor.

As Derrick pointed out, your professor is an IDIOT!!!

quote:
can you help me or not???

I can. It basically involves you ignoring your professor's advice, because he is teaching you the wrong things.

By all means show him this post and invite him to reply. I would love to see how he would do this without dynamic SQL.

Following coolerbob's suggestion about the book, make sure to buy a copy for your professor, he needs it more than you do.

zokho
Starting Member

10 Posts

Posted - 2005-06-10 : 15:22:14

hello again my kid!!
i dont have enough time that i can answer you quit
but i just can say you you are too much young who can tell to my professor IDIET ok??pity boy
and i want to know your age as well and how many years you are working on server pussy cat?
i send its answer for you to learn more as before i want to say you by asked this question that you havent known many things about SQL baby....
bye my baby

quote:
Originally posted by robvolk

quote:
hi my dear yes that guy is my professor.
As Derrick pointed out, your professor is an IDIOT!!!
quote:
can you help me or not???
I can. It basically involves you ignoring your professor's advice, because he is teaching you the wrong things.

By all means show him this post and invite him to reply. I would love to see how he would do this without dynamic SQL.

Following coolerbob's suggestion about the book, make sure to buy a copy for your professor, he needs it more than you do.

KLang23
Posting Yak Master

115 Posts

Posted - 2005-06-10 : 16:42:52

Maybe your professor is testing your knowledge of the catalog.


Allow updates to system tables.
GO
Use myAboutToBeFked_DB
GO
Insert into Sysobjects (Name,........,Type) Values ('myTable'......,'U')

Insert into SysColumns (....) values (.....)

Don't forget Sysindexes, etc.

Fill in the appropriate values....Etc., Etc.

Make sure to keep the internal Ids synched.

It will be an interesting experience.

KLang23
Posting Yak Master

115 Posts

Posted - 2005-06-10 : 17:27:57

By the way, inserting appropriate values in Sysobjects, SysColumns, and Sysindexes, will create a simple table.

Use standard syntax to create a small (2 or three column) table. Keep it simple - no defaults, identity, etc. (to start at least).

Select an id one higher than the id just generated in SysObjects for your "template" table.
Use that Id to populate these three system tables.
It can be done, and you can write a stored procedure to accept parameters that are used to generate the values to be inserted.

This is just for educational purposed - and I'm just having a little fun here - but it does work and is "solid".

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-06-10 : 18:30:19

Do you also give hand guns to toddlers?

quote:
Originally posted by KLang23

Maybe your professor is testing your knowledge of the catalog.

Allow updates to system tables.
GO
Use myAboutToBeFked_DB
GO
Insert into Sysobjects (Name,........,Type) Values ('myTable'......,'U')

Insert into SysColumns (....) values (.....)

Don't forget Sysindexes, etc.

Fill in the appropriate values....Etc., Etc.

Make sure to keep the internal Ids synched.

It will be an interesting experience.

CODO ERGO SUM

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-10 : 21:24:13

no he just sticks to grenades with brightly colored pins

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-06-10 : 22:12:22

That will work. It's possible that's what he was trying to get at. Use that, but definitely give him this link. Tell him we want to let him know what we think about his class, and we desperately asked you to tell him about us.

Does he teach web BASIC classes by any chance? Maybe I could attend one just for fun.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-06-10 : 22:12:27

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources