0

I have built up the T-SQL variable @Foo over many lines of code.

If @Foo is declared as VarChar(MAX), the immediately following PRINT statement outputs what I expect (5,421 characters).

However, the further following EXECUTE statement fails because it requires an NVarChar parameter.

When I change the declaration of @Foo to NVarChar(MAX), the PRINT statement only prints out a subset (3,902 characters) of what I am expecting. The output is truncated.

@Foo is the concatenation of a number of variables, all of which are VarChar(MAX). Before I go up the chain, changing all predecessor variables as NVarChar(MAX), to continue testing, I was wondering if anyone has an idea about this inconsistent behaviour.

PRINT @Foo

EXECUTE [master].[sys].[sp_executesql] @Foo

Many thanks.

Keith

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Keith Howard
  • 347
  • 4
  • 12

1 Answers1

0

As MSDN PRINT states,

A message string can be up to 8,000 characters long if it is a non-Unicode string, and 4,000 characters long if it is a Unicode string. Longer strings are truncated. The varchar(max) and nvarchar(max) data types are truncated to data types that are no larger than varchar(8000) and nvarchar(4000).

sp_executesql doesn't apply these restrictions.

Alex Kudryashev
  • 9,120
  • 3
  • 27
  • 36
  • Thanks Alex. So, do you have any idea how I could use sp_executesql to run a, say, 5,000 character T-SQL expression contained in a variable? I seem to be in a catch-22 - sp_executesql requires NVarChar, and NVarChar is capped at 4,000 characters. One hacky thing I could do would be to put the execute statement inside the loop where I am currently iteratively concatenating T-SQL code to cumulatively execute after the loop, but that is not ideal. Thanks. – Keith Howard Jun 16 '16 at 19:22
  • Ther is no such restriction for `sp_executesql`. See https://msdn.microsoft.com/en-us/library/ms188001.aspx – Alex Kudryashev Jun 16 '16 at 19:29
  • Hi Alex. I think that my user experience is inconsistent with your feedback. I get the following message when I execute a VarChar parameter: Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'. Also, this link seems to say the opposite: http://stackoverflow.com/questions/2743890/why-do-i-get-procedure-expects-parameter-statement-of-type-ntext-nchar-nvar – Keith Howard Jun 16 '16 at 20:08
  • As `sp_executesql` specification reads `@stmt` (first argument) must be nvarchar(length) `max` is ok. The value must start with N `declare @stmt nvarchar(max) = N'select....'`. "On 64-bit servers, the size of the string is limited to 2 GB, the maximum size of nvarchar(max)." Read the link above again. – Alex Kudryashev Jun 16 '16 at 20:18