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

Declare dynamicly variable

I'd like to know, if there is a possibility reference declare SQLServer dynamically?

For example, I've tried execute this query(under), but I received this message (Server: Msg 137, Level 15, State 1, Line 6
Must declare the variable '@.t1'.):

declare @.Frase varchar(50)
set @.Frase = 'declare @.t1 varchar(10), @.t2 int'
exec (@.Frase)
select @.t1 = 'AAAAA'
select @.t2 = 1000
Print @.t1
Print @.t2

ThanksMay be because Dynamic SQL is executed/Parsed last by the query Parser and the variable declared are within the statement which is limited to the "exec" and is considered a seperate stored procedure outside of main query|||The EXEC statement executes within its own scope, outside of the procedure that calls it. Therefore, EXEC cannot share variables with its calling procedure. As soon as EXEC completes, the variables go out of scope and "poof", they disappear. Temporary tables, however, are connection specific and can be referenced within EXEC statements.

blindman

Declare cursor based on dynamic query

Hi,

I am declaring the cursor based on a query which is generated dynamically. but it is not working

Declare @.tempSQL varchar(1000)

This query will be generated based on my other conditon and will be stored in a variable

set @.tempsql = 'select * from orders'

declare cursor test for @.tempsql

open test

This code is not working.

please suggest

Nitin

Hi

I am writing the code as below

Declare @.testSQl varchar(1000)

set @.testsql = 'select * from orders'

declare test1 cursor for @.testSQl

The declare statement is not working . My @.testsql will be generated at run time.

Help

Nitin

|||You can not use dynamic sql while opening the cursors..

it should be like this

Declare Test1 cursor for
Select * From Orders|||

You could add the cursor creation to your dynamic sql and then just call sp_executesql for the built up string. Something like...

DECLARE @.sql nvarchar(4000)

--Get beginning of cursor

SELECT @.sql = 'DECLARE c CURSOR FOR'

--Decision code for what query is built

SELECT @.sql = @.sql + 'SELECT * FROM orders'

--Remainder of cursor with specific columns from above query

SELECT @.sql = @.sql + 'OPEN c FETCH NEXT FROM c INTO ....'

--Execute the string we just built

EXEC sp_executesql @.sql

|||

I don't like to ever advocate the use of cursors, but you can do this using a global cursor, if you really must:

create procedure test
as
declare @.name nvarchar(128)
exec ('declare bob cursor global for select name from sys.objects')
open bob

fetch next from bob into @.name
select @.name as works
close bob
deallocate bob
go

test

|||

Hi,

I dont know for the moment how to declare a cursor on a query from a string.. I dont think its possible this way. An alternative is to find a solution other than using the cursor, else you'd lose development time in trying to find a solution.

If you cannot find a solution, try to explain the problem, someone will try help out, and also cursors generally tend to be less performant.

|||this is not possible. i agree with waaz|||

Instead of local cursor, you can create a Global cursor with dynamic sql, which is available beyond the scope the dynamic sql

like this

set @.sql='declare test cursor global for '+ @.tempsql

exec sp_executesql @.sql

open test

close test

|||You can use dynamic SQL to create a global cursor as shown in another reply in this thread. But what are you trying to do? Why do you need to use a cursor? And why do you need to use dynamic SQL? Both have performance implications. And dynamic SQL has serious security implications that can compromise your database system and/or network. You will have to use techniques (both in the database and client-side depending on how you call your SP) that avoid SQL injection to protect your database and network from malicious users. Apart from these problems, dynamic SQL requires more maintainence because you have to grant more permissions to end users since checks are deferred to run-time unlike SPs with static SQL statements. So it is easy to create a cursor dynamically but that is not the right thing to do in majority of the cases.|||

Also, try not to ask the same question twice. This question was also answered in another thread. I have merged the threads into one.

|||

hey whitney,

i got the same problem of dynamic query with cursors..

You gave the alternative but i got the big cursor and its difficult for me to put the entire stuff in string.

Because it gets difficult to maintain for me.

Any help or comment regarding this will be appreciated.

Thanks a ton!!

dromyl@.hotmail.com