Click here to Skip to main content
14,691,039 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
hi,

i need a store procedure to create table dynamically
based on the value in temp table
i.e
if i pass 5 then table should be like
colname ='col'+ value
col1,col2,col3,col4,col5 all of them of same datatype

am using the below SP to alter the table
Create proc [dbo].[altertemp1](@Value_c varchar)
AS
BEGIN
DECLARE @intFlag INT
DECLARE @ColName nvarchar(100)
set @ColName='col'
DECLARE @DynamicSQL nvarchar(250)
SET @intFlag = 3
WHILE (@intFlag <=@Value_c)
BEGIN
set @ColName= @ColName + cast(@intFlag as nvarchar(100))
SET @DynamicSQL = 'ALTER TABLE ##Mytemp ADD ['+ CAST(@ColName AS nvarchar(100)) +'] binary(100) NULL'
EXEC(@DynamicSQL)
SET @intFlag = @intFlag + 1

end

END


the above SP am trying am getting like if i pass value 5 then mu out put is like col1,col12,col123 .....,any suggestion in my sp,any other method to do this process

thanks in advance
Posted
Updated 21-Dec-11 6:16am
v3

1 solution

Try something like:
CREATE PROCEDURE altertemp1(@ColNumMax int)
AS
BEGIN
   DECLARE @counter INT
   DECLARE @ColName nvarchar(100)
   DECLARE @DynamicSQL nvarchar(250)

   SET @counter = 1
   WHILE (@counter <= @ColNumMax) BEGIN
      SET @ColName= 'Col' + cast(@counter as nvarchar(100))
      SET @DynamicSQL = 'ALTER TABLE ##Mytemp ADD ['+ @ColName +'] binary(100) NULL'
      EXEC(@DynamicSQL)
      SET @counter = @counter + 1
   END
END
GO
   

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900