Dynamically adding columns to the table at runtime

Question

4.00/5 (1 vote)

See more:

Hi ,
am creating a temporary table in my application
am checking the table first and creating it with a sp

SQL

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

SQL

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
thanks in advance :)

Posted 17-Nov-11 22:42pm

pradeep manne

Updated 18-Nov-11 0:56am

Pandya Anil

v3

Add a Solution

Comments

Mehdi Gholam 18-Nov-11 4:54am

EDIT -> fixed formatting

Pandya Anil 18-Nov-11 6:59am

what you want solution or suggestion over your given solution ?

RDBurmon 12-Dec-11 6:44am

Have you encountered with any error related with your queries ? Please post here if any

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Shanalal Kasim · Answer 1 · 2012-10-29T01:44:00

Solution 1

Refer below link

http://forums.asp.net/t/1593035.aspx/1[^]

Posted 29-Oct-12 1:44am

Shanalal Kasim

BK 4 code · Answer 2 · 2012-10-29T02:42:00

SQL

SELECT count(COLUMN_NAME) FROM INFORMATION_SCHEMA.Columns where TABLE_NAME = 'LOGINTABLE'

create proc [dbo].[temp1]
as

Begin
DECLARE @ColName nvarchar(100)
DECLARE @DynamicSQL nvarchar(250)
DECLARE @pTOTALCOLUMN INT
SELECT @pTOTALCOLUMN=count(COLUMN_NAME) FROM INFORMATION_SCHEMA.Columns where TABLE_NAME = '##Mytemp'
SET @ColName='COL' + cast(@pTOTALCOLUMN as varchar(10))
SET @DynamicSQL = 'ALTER TABLE ##Mytemp ADD ['+ CAST(@ColName AS nvarchar(100)) +'] nvarchar(100) NULL'
EXEC(@DynamicSQL)
End