Click here to Skip to main content
15,664,823 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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

Arun R.V

1 solution

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

for More information read this article

Building Dynamic SQL In a Stored Procedure[^]
Share this answer

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