dynamic sql string too long

[Michael Zdarsky]

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

[Chris2]

Post by Michael Zdarsky
hello,
i have a stored procedure, which is building a dynamic sql

statement with

Post by Michael Zdarsky
and than
i do an
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.

[Michael Zdarsky]

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, through
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

Post by Michael Zdarsky

Post by Michael Zdarsky
hello,
i have a stored procedure, which is building a dynamic sql

statement with

Post by Michael Zdarsky
and than
i do an
The problem ist, that the statement now exceeds the maximum length

of 4000

Post by Michael Zdarsky
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.

[Erland Sommarskog]

Post by Michael Zdarsky
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.

[Aaron Bertrand [SQL Server MVP]]

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

Post by Michael Zdarsky
hello,
i have a stored procedure, which is building a dynamic sql statement with
and than
i do an
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]

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, through
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

Post by Aaron Bertrand [SQL Server MVP]
Use multiple variables.
@st2 NVARCHAR(4000);
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

Post by Michael Zdarsky
hello,
i have a stored procedure, which is building a dynamic sql statement with
and than
i do an
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

[Aaron Bertrand [SQL Server MVP]]

Post by Michael Zdarsky
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

[Erland Sommarskog]

Post by Michael Zdarsky
i have a stored procedure, which is building a dynamic sql statement with
and than
i do an
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.