在运行时将列动态添加到表中 [英] Dynamically adding columns to the table at runtime

问题描述

在我的应用程序中创建一个临时表
我先检查表并用sp
创建表

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

我需要根据值添加二进制数据类型的列
如果值大于3(即4)，则应添加列，如
col + maxnumber

以下是我的样本sp

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

此sp将在我的脚本中创建1个colnedd更正的错误，请给我任何建议
在此先感谢:)

this sp will create 1 col nedd some more correction in my script plz give me any suggestion
thanks in advance :)

推荐答案

请参阅以下链接

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

Refer below link

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

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

这篇关于在运行时将列动态添加到表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！