2

I'm using dynamic SQL and I need to exec a long SQL query, First I declare @var with query as nvarchar(4000), but my query is longer than 4000 chars. I try to change to nvarchar(8000) but raise an Exception that can't be longer than 4000 chars.

Finally I declare var as varchar(8000) and no error ocurrs , but when I going to exe sp_executeSQL raise and error that sp_executeSQL expect a ntext/nchar/nvarchar.

How I can exec a longer Sql Query with sp_executeSQL ? Thnks!

I'm using Sql Server Express 2005.

arturn
  • 725
  • 2
  • 11
  • 25

1 Answers1

4

As you are on 2005 you can use declare @var nvarchar(max) for up to 1 billion characters.

You will find using PRINT statements doesn't work to view the variable contents for very long strings so you can do 

SELECT @var AS [processing-instruction(x)] FOR XML PATH('')

To see the untruncated contents.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • 1
    This is THE answer for debugging dynamic sql! Thank you so much. I found another answer that used a similar XML approach, but the output was encoded (e.g. "<>" would appear as "lt;gt;"). Your approach does not have that problem AND I can view the dynamic code with line-breaks (instead of it jumbled into one long line). – MikeTeeVee Nov 04 '11 at 21:30