动态创建表格 [英] Dynamically creating table

问题描述

我需要一个存储过程来动态创建表
基于临时表中的值
即
如果我通过5，那么表格应该像
colname ="col''+值
col1，col2，col3，col4，col5都具有相同的数据类型

我正在使用以下SP来更改表格

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

上面的SP正在尝试获得如果我传递值5然后mu输出就像col1，col12，col123 .....，在我的sp中有任何建议，任何其他执行此过程的方法

在此先感谢

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

推荐答案

尝试类似的方法:

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

这篇关于动态创建表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！