Declare Variable Dynamically

I'm attempting to modify some Crosstab generating code, and I need some
advice/examples.

Currently, the code uses a single string variable to store the
dynamically generated query (www.johnmacintyre.ca). The problem is that
I am trying to pivot biological taxonomy information, and may end up
with a table containing over 200 columns. This takes the dynamic string
well over the 8000char limit for variables.

>From my understanding, the EXEC() command does not have the 8000char
limit if the execution string is broken into chunks, and concatenated
e.g. EXEC(sql1 + sql2 + sql3 + ...). So the solution I think I need is
to:

1) start a counter at the beginining of the dynamic generation
2) append the counter value to the end of a string variable name
3) DECLARE the new variable and attach that loop cycle of text to it,
or attach each chunk of characters < 8000
4) build the EXEC() string by concatenating each dynamic varible

Can this be done? Should it be done? Is there a better way to address
this type of problem?

Thanks for any ideas or insights

Tim Pascoe>> Can this be done? <<

Maybe, maybe not.

>> Should it be done? <<

No, this is a mis-use of SQL.

>> Is there a better way to address this type of problem? <<

Crosstabs (thank you for not calling them "pivot tables") are a report
and not a query. you ought to use a report tool and not SQL for this
kind of job.|||Hi
You may want to look at previous posts regarding crosstab queries, as Joe
says it is better to do this in the reporting tool such as RAC
http://www.rac4sql.net/ and others.

Here are a few links you may want to read:
http://www.windowsitpro.com/SQLServ...5608/15608.html
http://support.microsoft.com/defaul...b;EN-US;q175574

http://www.sqlteam.com/item.asp?ItemID=2955

John

<tim.pascoe@.cciw.ca> wrote in message
news:1102971154.140806.89470@.f14g2000cwb.googlegro ups.com...
> I'm attempting to modify some Crosstab generating code, and I need some
> advice/examples.
> Currently, the code uses a single string variable to store the
> dynamically generated query (www.johnmacintyre.ca). The problem is that
> I am trying to pivot biological taxonomy information, and may end up
> with a table containing over 200 columns. This takes the dynamic string
> well over the 8000char limit for variables.
>>From my understanding, the EXEC() command does not have the 8000char
> limit if the execution string is broken into chunks, and concatenated
> e.g. EXEC(sql1 + sql2 + sql3 + ...). So the solution I think I need is
> to:
> 1) start a counter at the beginining of the dynamic generation
> 2) append the counter value to the end of a string variable name
> 3) DECLARE the new variable and attach that loop cycle of text to it,
> or attach each chunk of characters < 8000
> 4) build the EXEC() string by concatenating each dynamic varible
> Can this be done? Should it be done? Is there a better way to address
> this type of problem?
> Thanks for any ideas or insights
> Tim Pascoe|||RAC was my next point of investigation. The problem is that I'm not
actually producing a report of the data (although I agree this is
generally what crosstabs are for). Instead, the data is required in
this format so it can be fed into statistical software, which requires
the crosstabulated structure. I'll look into RAC and the other sites
you listed.

Thanks.

John Bell wrote:
> Hi
> You may want to look at previous posts regarding crosstab queries, as
Joe
> says it is better to do this in the reporting tool such as RAC
> http://www.rac4sql.net/ and others.
> Here are a few links you may want to read:
http://www.windowsitpro.com/SQLServ...5608/15608.html
> http://support.microsoft.com/defaul...b;EN-US;q175574
> http://www.sqlteam.com/item.asp?ItemID=2955
> John
> <tim.pascoe@.cciw.ca> wrote in message
> news:1102971154.140806.89470@.f14g2000cwb.googlegro ups.com...
> > I'm attempting to modify some Crosstab generating code, and I need
some
> > advice/examples.
> > Currently, the code uses a single string variable to store the
> > dynamically generated query (www.johnmacintyre.ca). The problem is
that
> > I am trying to pivot biological taxonomy information, and may end
up
> > with a table containing over 200 columns. This takes the dynamic
string
> > well over the 8000char limit for variables.
> >>From my understanding, the EXEC() command does not have the
8000char
> > limit if the execution string is broken into chunks, and
concatenated
> > e.g. EXEC(sql1 + sql2 + sql3 + ...). So the solution I think I need
is
> > to:
> > 1) start a counter at the beginining of the dynamic generation
> > 2) append the counter value to the end of a string variable name
> > 3) DECLARE the new variable and attach that loop cycle of text to
it,
> > or attach each chunk of characters < 8000
> > 4) build the EXEC() string by concatenating each dynamic varible
> > Can this be done? Should it be done? Is there a better way to
address
> > this type of problem?
> > Thanks for any ideas or insights
> > Tim Pascoe|||Hi

You may want to consider Analysis services then?

John

<tim.pascoe@.cciw.ca> wrote in message
news:1103032212.575699.29580@.c13g2000cwb.googlegro ups.com...
> RAC was my next point of investigation. The problem is that I'm not
> actually producing a report of the data (although I agree this is
> generally what crosstabs are for). Instead, the data is required in
> this format so it can be fed into statistical software, which requires
> the crosstabulated structure. I'll look into RAC and the other sites
> you listed.
> Thanks.
>
> John Bell wrote:
>> Hi
>> You may want to look at previous posts regarding crosstab queries, as
> Joe
>> says it is better to do this in the reporting tool such as RAC
>> http://www.rac4sql.net/ and others.
>>
>> Here are a few links you may want to read:
>>
> http://www.windowsitpro.com/SQLServ...5608/15608.html
>> http://support.microsoft.com/defaul...b;EN-US;q175574
>>
>> http://www.sqlteam.com/item.asp?ItemID=2955
>>
>> John
>>
>> <tim.pascoe@.cciw.ca> wrote in message
>> news:1102971154.140806.89470@.f14g2000cwb.googlegro ups.com...
>> > I'm attempting to modify some Crosstab generating code, and I need
> some
>> > advice/examples.
>>> > Currently, the code uses a single string variable to store the
>> > dynamically generated query (www.johnmacintyre.ca). The problem is
> that
>> > I am trying to pivot biological taxonomy information, and may end
> up
>> > with a table containing over 200 columns. This takes the dynamic
> string
>> > well over the 8000char limit for variables.
>>> >>From my understanding, the EXEC() command does not have the
> 8000char
>> > limit if the execution string is broken into chunks, and
> concatenated
>> > e.g. EXEC(sql1 + sql2 + sql3 + ...). So the solution I think I need
> is
>> > to:
>>> > 1) start a counter at the beginining of the dynamic generation
>> > 2) append the counter value to the end of a string variable name
>> > 3) DECLARE the new variable and attach that loop cycle of text to
> it,
>> > or attach each chunk of characters < 8000
>> > 4) build the EXEC() string by concatenating each dynamic varible
>>> > Can this be done? Should it be done? Is there a better way to
> address
>> > this type of problem?
>>> > Thanks for any ideas or insights
>>> > Tim Pascoe
>|||(tim.pascoe@.cciw.ca) writes:
> I'm attempting to modify some Crosstab generating code, and I need some
> advice/examples.
> Currently, the code uses a single string variable to store the
> dynamically generated query (www.johnmacintyre.ca). The problem is that
> I am trying to pivot biological taxonomy information, and may end up
> with a table containing over 200 columns. This takes the dynamic string
> well over the 8000char limit for variables.
>>From my understanding, the EXEC() command does not have the 8000char
> limit if the execution string is broken into chunks, and concatenated
> e.g. EXEC(sql1 + sql2 + sql3 + ...). So the solution I think I need is
> to:
> 1) start a counter at the beginining of the dynamic generation
> 2) append the counter value to the end of a string variable name
> 3) DECLARE the new variable and attach that loop cycle of text to it,
> or attach each chunk of characters < 8000
> 4) build the EXEC() string by concatenating each dynamic varible
> Can this be done? Should it be done? Is there a better way to address
> this type of problem?

I think it can be done, but I would not like to do it. You would have
to generate dynamic SQL which in its turn generates the dynamic SQL
that executes the query.

It may be a better alternative to use a client language to generate the
SQL. If you use a language like Perl or Visual Basic which has unlimited
strings, you are saved the restriction of varchar(8000). Note that all
that you would bring to the client would be the meta data needed to
form the SQL statement.

In SQL 2005 there is a new data type varchar(MAX) which is akin to text,
but works more like varchar, and thus in SQL 2005 you would also be saved
from the varchar(8000) restriction. SQL 2005 is currently in beta, with
release planned next year.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp