Click here to Skip to main content
14,698,806 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[^]

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