在SQL中动态创建表的声明 [英] Decleration of Dynamically created table in SQL

问题描述

大家好
在我的项目中，我想声明一个临时表，该表的列数根据数据计数而变化.例如，如果我是第一次使用数据
像这样的A，B，C，D，然后我想创建像这样的列

 声明  @ TableName  表(名称 varchar ( 20 )，A  int ，B  int ，C  int ，D  int )

然后第二次像这样在A，B，C，D，E，F，G上具有Seri​​es
现在在这个时候我需要声明这样的表

 声明  @ TableName  表(名称 varchar ( 20 )，A  int ，B  int ，C  int ，D  int ，E  int ，F  int ，G   set  @ appendedString = cast( @ DistinctNoOfShareShareColumnName   varchar ('  A  int ，B  int ，C  int ，E  int ，G   set  @ TableQuery = ' 声明@MainOverViewTable表(DirectorName varchar(40)，' + @ appendedString + ' )' 

但这没用.然后我像这样尝试

 声明  @ MainOverViewTable  表(DirectorName  varchar ( 40 )， @ appendedString )

但是这东西也没用

因此，如何在SP中用列名称的变体声明动态表

如果有人知道，请给我一些指导或建议.在此先感谢

问候
Arun R.V

解决方案

您可以像下面的示例一样使整个sp动态sql

 声明  @ TableQuery   nvarchar ( 200 )， @ appendedString   nvarchar ( 200 )
集合  @ appendedString  = '  A int，B int，C int，D int，E int，F int，G int'
 set  @ TableQuery = ' 声明@MainOverViewTable表(DirectorName varchar (40)，' + @ appendedString + ' )选择* from @MainOverViewTable'
打印  @ TableQuery 
 exec ( @ TableQuery )

有关更多信息，请阅读本文

在存储过程中构建动态SQL [ ^ ]

Hello every one
I my project I want to declare a temporary table where its columns count changes based on data count .For example if I am having data for first time
like this A,B,C,D like this then I want to create column like this

Declare @TableName table(name varchar(20),A int,B int,C int,D int)

Then for second time it will be like this having Series on A,B,C,D,E,F,G
now at this time i need to declare table like this

Declare @TableName table(name varchar(20),A int,B int,C int,D int,E int,F int,G int)

So what I am telling here is my columns count varies based on I get the data

for that I am doing some loop operation and using string appending concept

set @appendedString=cast(@DistinctNoOfShareShareColumnName as varchar(10))+' int,'+@appendedString

here in @appendedString variable i am getting value like this

A int,B int,C int,D int,E int,F int,G int

Now i want to declare a variable but I tried like this

set @TableQuery='declare @MainOverViewTable table (DirectorName varchar(40),'+@appendedString +')'

But it did''t work .Then I tried like this

declare @MainOverViewTable table (DirectorName varchar(40),@appendedString )

But this thing also did''t work

So how can I declare a Dynamic table in SP with variation of columns name

If any body know give me some guidance or suggestion . Thanks in advance

Regards
Arun R.V

解决方案

you can Make your whole sp dynamic sql like this example

declare  @TableQuery nvarchar(200),@appendedString nvarchar(200)
set @appendedString = 'A int,B int,C int,D int,E int,F int,G int'
set @TableQuery='declare @MainOverViewTable table (DirectorName varchar(40),'+@appendedString +') select *  from @MainOverViewTable '
print @TableQuery
exec(@TableQuery)

for More information read this article

Building Dynamic SQL In a Stored Procedure[^]

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