creating a stored procedure to create a table

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

Transact-SQL (2008)

creating a stored procedure to create a table

Author

Topic

pradeepmanne
Starting Member

31 Posts

Posted - 2012-01-18 : 10:22:36

hi,
need to create permanent tables dynamically
plz guid me in this process
i can pass datatypes as parameters ,

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2012-01-18 : 10:25:08

Hello pradeepmanne,

The user will need the appropriate permissions and the proc will require D-SQL to construct the DDL call.

HTH.

pradeepmanne
Starting Member

31 Posts

Posted - 2012-01-18 : 10:29:15

hi ,thanks for the reponse
below is my sp to create a table but of all varchar datatype
plz suggest me how create columns of different datatypes

ALTER procedure [dbo].[sp_final_tbl](@max_cols int)
as
begin

IF OBJECT_ID('finaltbl') IS NULL

CREATE TABLE finaltbl(Col1 varchar(1000))
else if OBJECT_ID('finaltbl') IS NOT NULL

DECLARE @counter INT
DECLARE @ColName nvarchar(100)

DECLARE @DynamicSQL nvarchar(250)

SET @counter = 2
WHILE (@counter <= @max_cols)
BEGIN
SET @ColName= 'Col' + cast(@counter as nvarchar(100))

SET @DynamicSQL = 'ALTER TABLE finaltbl ADD ['+ @ColName +'] varchar(100) NULL'
EXEC(@DynamicSQL)
SET @counter = @counter + 1
END
end

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-18 : 11:11:30

quote:
Originally posted by pradeepmanne

hi,
need to create permanent tables dynamically
plz guid me in this process
i can pass datatypes as parameters ,

No you don't. Or at *least* you really, really shouldn't

what are you actually trying to do? Maybe we can suggest something less dangerous.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

pradeepmanne
Starting Member

31 Posts

Posted - 2012-01-18 : 11:16:43

hi,
the above sp will create table based on the max column count
in my front end i can find column datatype based on that column has to be created

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-18 : 11:20:47

No, you misunderstand me.

Why do you want to create a NEW PERMANENT TABLE based on that. Creating tables is something that you should carefully design. If you want to store results of some operation then you should have a table or set of tables that model that result set up already.

If you create tables dynamically your database will end up in a mess, you won't be easily able to relate any related information, and your model of the world will be all over the place.

We could probably help you do whatever it is you want to do in a better way. A way that doesn't involve making new tables on the fly.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

pradeepmanne
Starting Member

31 Posts

Posted - 2012-01-18 : 11:33:38

[quote]Originally posted by pradeepmanne

hi,
i know it, u r right but my need demands like that,

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-18 : 11:36:33

[code]
SELECT *
INTO
<NameOFPermanentTableHere>
FROM
<TheTempTable>
[/code]

Example:
[code]

BEGIN TRAN

-- This will return no rows
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE [TABLE_NAME] = '_PERM_FOO'

-- Make temp table
DECLARE @foo TABLE ([val] INT)
INSERT @foo ([val]) VALUES (1),(2)

-- Populate real table
SELECT * INTO _PERM_FOO FROM @foo

-- This will now produce 1 row
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE [TABLE_NAME] = '_PERM_FOO'

--UNDO, UNDO, UNDO
ROLLBACK
[/code]
Good luck. I think you are doing the wrong thing

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-18 : 12:51:23

Next Stop...Mars

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

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-01-18 : 14:45:26

If by Mars you mean a trip to the hard drive store to get a few petabytes worth of drives for this database, i agree.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources