Decleration of Dynamically created table in SQL

Question

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

SQL

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

SQL

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

SQL

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

here in @appendedString variable i am getting value like this

SQL

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

SQL

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

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

SQL

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 30-Oct-12 0:14am

arunrv

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Ahmedwaheed · Answer 1 · 2012-10-30T03:29:00

you can Make your whole sp dynamic sql like this example

SQL

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