dynamic sql string too long

hello,
i have a stored procedure, which is building a dynamic sql statement with
DECLARE @.myStatement NVARCHAR( 4000 )
SET @.myStatement = 'blablabla'
and than
i do an
EXEC ( @.myStatement )
The problem ist, that the statement now exceeds the maximum length of 4000
nvarchars.
How can I create statements which are longer than 4000?
Switching to varchar is not a good solution.
Currently my statment is about 80.000 characters long!
thank you
michael
Michael Zdarsky"Michael Zdarsky" <zdarsky_at_zac_dash_it_dot_com> wrote in message
news:367E3B17-2F65-43AC-9D8C-E2F3C0D2542D@.microsoft.com...
> hello,
> i have a stored procedure, which is building a dynamic sql
statement with
> DECLARE @.myStatement NVARCHAR( 4000 )
> SET @.myStatement = 'blablabla'
> and than
> i do an
> EXEC ( @.myStatement )
> The problem ist, that the statement now exceeds the maximum length
of 4000
> nvarchars.
> How can I create statements which are longer than 4000?
> Switching to varchar is not a good solution.
> Currently my statment is about 80.000 characters long!
> thank you
> michael
Michael Zdarsky,
An eighty thousand character SQL string?
May I ask why it is that long?
If you have SQL Server 2005, you may use NVARCHAR(MAX).
Sincerely,
Chris O.|||Use multiple variables.
DECLARE @.st2 NVARCHAR(4000),
@.st2 NVARCHAR(4000);
EXEC(@.st1+@.st2);
Or, find ways around making such a long string. Why is your query so long?
A massive IN() list? See http://www.aspfaq.com/2248 for some elegant ways
around that.
A
"Michael Zdarsky" <zdarsky_at_zac_dash_it_dot_com> wrote in message
news:367E3B17-2F65-43AC-9D8C-E2F3C0D2542D@.microsoft.com...
> hello,
> i have a stored procedure, which is building a dynamic sql statement with
> DECLARE @.myStatement NVARCHAR( 4000 )
> SET @.myStatement = 'blablabla'
> and than
> i do an
> EXEC ( @.myStatement )
> The problem ist, that the statement now exceeds the maximum length of 4000
> nvarchars.
> How can I create statements which are longer than 4000?
> Switching to varchar is not a good solution.
> Currently my statment is about 80.000 characters long!
> thank you
> michael
>
> --
> Michael Zdarsky
>|||Michael Zdarsky (zdarsky_at_zac_dash_it_dot_com) writes:
> i have a stored procedure, which is building a dynamic sql statement with
> DECLARE @.myStatement NVARCHAR( 4000 )
> SET @.myStatement = 'blablabla'
> and than
> i do an
> EXEC ( @.myStatement )
> The problem ist, that the statement now exceeds the maximum length of 4000
> nvarchars.
> How can I create statements which are longer than 4000?
> Switching to varchar is not a good solution.
> Currently my statment is about 80.000 characters long!
80000? Ouch!
Well, you can do
EXEC(@.sql1 + @.sql2 + @.sql3 + ...)
In SQL 2005 you can you use nvarchar(MAX), which makes things a lot simpler.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hello Aaron,
thank you for your answer.
This is a high complex query which is generated depending on user input.
the user input is sort of filter criterion for a 2GB database to search
through
special entries.
You are right with massive in list, but the problem is,
that each in list can be longer than 4000 NVARCHAR, so I have to split the
inlists in different variables.
But the in list problem is only one part of the problem.
The filter is realy complex. I haven't seen more complex queries before.
And the greatest problem is the mass data input in that query by user, throu
gh
selection lists on the UI.
I can not work with ids, because the selected data from the UI can be saved
and reused with a different database.
The id's of the selected data wouldn't match, so I have to use the pure UI
data.
Thanks
Michael
Michael Zdarsky
"Chris2" wrote:

> "Michael Zdarsky" <zdarsky_at_zac_dash_it_dot_com> wrote in message
> news:367E3B17-2F65-43AC-9D8C-E2F3C0D2542D@.microsoft.com...
> statement with
> of 4000
> Michael Zdarsky,
> An eighty thousand character SQL string?
> May I ask why it is that long?
> --
> If you have SQL Server 2005, you may use NVARCHAR(MAX).
>
> Sincerely,
> Chris O.
>
>|||Hello Aaron,
thank you for your answer.
This is a high complex query which is generated depending on user input.
the user input is sort of filter criterion for a 2GB database to search
through
special entries.
You are right with massive in list, but the problem is,
that each in list can be longer than 4000 NVARCHAR, so I have to split the
inlists in different variables.
But the in list problem is only one part of the problem.
The filter is realy complex. I haven't seen more complex queries before.
And the greatest problem is the mass data input in that query by user, throu
gh
selection lists on the UI.
I can not work with ids, because the selected data from the UI can be saved
and reused with a different database.
The id's of the selected data wouldn't match, so I have to use the pure UI
data.
Thanks
Michael
--
Michael Zdarsky
"Aaron Bertrand [SQL Server MVP]" wrote:

> Use multiple variables.
> DECLARE @.st2 NVARCHAR(4000),
> @.st2 NVARCHAR(4000);
> EXEC(@.st1+@.st2);
> Or, find ways around making such a long string. Why is your query so long
?
> A massive IN() list? See http://www.aspfaq.com/2248 for some elegant ways
> around that.
> A
>
> "Michael Zdarsky" <zdarsky_at_zac_dash_it_dot_com> wrote in message
> news:367E3B17-2F65-43AC-9D8C-E2F3C0D2542D@.microsoft.com...
>
>|||> You are right with massive in list, but the problem is,
> that each in list can be longer than 4000 NVARCHAR, so I have to split the
> inlists in different variables.
So if you already have them in separate variables, can't you still dump the
list into table form, a la http://www.aspfaq.com/2248 ?
A|||Michael Zdarsky (zdarsky_at_zac_dash_it_dot_com) writes:
> You are right with massive in list, but the problem is,
> that each in list can be longer than 4000 NVARCHAR, so I have to split the
> inlists in different variables.
If your dynamic SQL is only for lists for IN, you are barking up the
wrong tree. Not only is it difficult to manage, you get awful performance.
Have a look at http://www.sommarskog.se/arrays-in-sql.html for several
alternative methods. And you can use ntext for your lists.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx