append to nText

[dky1e]

Hi, I need help!

I need to use a local variable of length greater than 4k characters. The only way to accomplish this is to use the nText field (I need nText because i'm buildig a dynamic string for a query that will be then executed by sp_executesql).

Since i cant declare an ntext variable I had to create a temp table that stores this value. Now, I have a loop that needs to append to the value stored in the nText field.

Is it possible?


-----
What would be a better solution?

 

[btgreen]

What front end are you using to store this 4k value?

 

[btgreen]

Have you tried using string?

I have built 4k strings before and then call exec(string)

 

[dky1e]

SQL Server. There is not a String data type.

 

[LFCfan]

if this is any help, the varchar datatype goes up to 8000 characters

 

[SQLSister]

You could try putting parts of the string into different variables (one for the columns, one for the joins, one for the where clause, one for the group on clause, etc.) and then concatenating them together to execute. But honestly how many SQL strings are more than 8000 characters? We have a search with 30 different possible input values and 5 related tables and our search strings never get that long.

 

[tlbroadbent]

Couold you use something like the following?

Create uspYourProcName
@sql1 nvarchar(4000),
@sql2 nvarchar(4000),
@sql3 nvarchar(4000)
As

exec ('sp_executesql @statement=N'''+@sql1+@sql2+@sql3+'''') If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm