Dynamically adding columns to the 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)

Dynamically adding columns to the table

Author

Topic

pradeepmanne
Starting Member

31 Posts

Posted - 2011-11-18 : 04:17:37

Hi ,
am creating a temporary table in my application
am checking the table first and creating it with a sp
create proc [dbo].[temp]
as

Begin
IF EXISTS
(
SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..##Mytemp')

)

DROP TABLE ##Mytemp
ELSE

CREATE TABLE ##Mytemp(Col1 binary(100), Col2 binary)
End

i need to add columns of binary datatype based on a value
if the value is greatethan 3(i.e 4 ) then it should add column like
col+ maxnumber

below is my sample sp

create proc [dbo].[temp1]
as

Begin
DECLARE @ColName nvarchar(100)
DECLARE @DynamicSQL nvarchar(250)
SET @ColName='col4'
SET @DynamicSQL = 'ALTER TABLE ##Mytemp ADD ['+ CAST(@ColName AS nvarchar(100)) +'] nvarchar(100) NULL'
EXEC(@DynamicSQL)
End

this sp will create 1 col nedd some more correction in my script plz give me any suggestion
thnaks inadvance

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-11-18 : 05:32:47

Take a step back and explain what it is you actually want to do rather then how you've decided to do it.

I'm guessing you are pivoting some dataset? If so then there are much better ways of doing it that this.

Post some sample data and expected output.

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

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-18 : 05:39:07

global temp table not temp table.
That should work as long as the connection that created the table is still valid otherwise the table will be dropped - what is the problem?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources