Click here to Skip to main content
14,665,306 members
Rate this:
Please Sign up or sign in to vote.
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

Regards
Arun R.V
Posted

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

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[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100