very large Dynamic SQL statement but limited variable size?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Thomas Malia - 24 Jul 2008 20:42 GMT

I want to dynamically build and execute some DDL statements.

For example I want to cycle through all the databases listing in a table
where each of these databases have the same schema.
Then I want to build a view in one database that is the union of the data
from a given table that exists in all the databases.

That's roughly what I want to do, but that's not really the main issue I'm
running into. My problem is, I'm using a local character string variable to
hold the DDL string that I'm building as I cycle through the databases then
I use EXEC(@CMD) to execute the DDL.
Kinda like this:

DECLARE @CMD char(8000)
DECLARE @DBNAME char(256)

set @CMD = 'CREATE VIEW ALLDBS_TABLENAME AS '
--For each @DBNAME (using a cursor to loop throug)
BEGIN
set @CMD = @CMD + 'Select ' + @DBNAME + ','
set @CMD = @CMD + '{big long list of fields}'
set @CMD = @CMD + ' from '
set @CMD = @CMD + @DBNAME + '.dbo.TableName'

FETCH NEXT INTO @DBNAME
IF FETCH_STATUS = 0
SET @CMD = @CMD + 'UNION'
END
EXEC(@CMD)

The problem I ran into is, my command string is getting longer than 8000
characters sometimes. Anyone have any suggestions on how I can work around
this?

Thanks in advance,
Tom Malia

Reply to this Message

Aaron Bertrand [SQL Server MVP] - 24 Jul 2008 20:53 GMT

In SQL Server 2005, you can use VARCHAR(MAX) and avoid all of this nonsense
(unless your string becomes > 2 GB).

If you are stuck on 2000 (it is always useful to tell us your version!), you
can use multiple @CMD strings, e.g.

DECLARE @cmd1 VARCHAR(8000), -- NOT CHAR!
@cmd2 VARCHAR(8000) --, etc.

How many you need will depend on how many columns you have in your list. As
you loop, check the length of the variable, and when it is getting close to
8000, move to the next variable.

But then you can execute them using

EXEC(@cmd1 + @cmd2 + @cmd3 + ...);

On 7/24/08 3:42 PM, in article Or$BqVc7IHA.4112@TK2MSFTNGP05.phx.gbl,

> I want to dynamically build and execute some DDL statements.
>
[quoted text clipped - 32 lines]
> Thanks in advance,
> Tom Malia

Reply to this Message

Thomas Malia - 24 Jul 2008 21:15 GMT

FANTASTICE! Thank you very much!

BTW, on 2005 but always like to make my code reverse compatable with 2000
when possible so I'll consider the multple string variable approach

> In SQL Server 2005, you can use VARCHAR(MAX) and avoid all of this
> nonsense
[quoted text clipped - 59 lines]
>> Thanks in advance,
>> Tom Malia

Reply to this Message