sql-server - NVARCHAR(MAX) - 作为 SQL 存储过程输出参数

Question

Display_Info是一个SQL存储过程，有三个输入参数和三个输出参数。 info_Data(序列化信息数据也可能包含unicode和空值)其中一个输出参数之前是NVARCHAR(1000)类型。由于 info_Data 的大小现在已更改为 NVARCHAR(MAX) 类型。当它像 NVARCHAR(1000) 时，在客户端应用程序中执行存储过程没有问题，但在将其更改为 NVARCHAR(MAX) 之后，客户端应用程序抛出错误，如“至少一个参数包含不支持的类型”。 SQL存储过程设计如下图所示。

Create Display_Info @channel NVARCHAR(100)
    ,@infoType INT
    ,@locationId NVARCHAR(50)
    ,@Id BIGINT OUTPUT
    ,@infoData NVARCHAR(MAX) OUTPUT
    ,@infoStatus TINYINT OUTPUT
AS
...

客户端应用程序执行存储过程的方式是，

try
{
SACommand conncmd;
CheckConnection();
conncmd.setConnection(&mConn);
std::wstring cmdText = COMMAND_TEXT("ReadMessage");
conncmd.setCommandText(cmdText.c_str());
conncmd.Param("channel").setAsString() = SAString(channel.c_str(), (int)channel.length());
conncmd.Param("infoType").setAsNumeric() = SANumeric((sa_int64_t)type);
conncmd.Param("locationId").setAsString() = SAString(locationId.c_str(), (int)locationId.length());
conncmd.Execute(); 
std::wstring Id = conncmd.Param(COMMAND_TEXT("Id")).asString();
infodata = conncmd.Param(COMMAND_TEXT("info_Data")).asString();
}
catch (SAException &e)
{
std::string errorMessage = (mb_twine)e.ErrText();
std::cout << "\n" <<errorMessage;
}

示例输入/输出:

infoData 序列化输入:总长度5191

Ä(Á(¼(Protocol Buffers is a method of serializing structured data. It is useful in developing programs to communicate with each other over a wire or for storing data. The method involves an interface description language that describes the structure of some data and a program that generates source code from that description for generating or parsing a stream of bytes that represents the structured data.Google developed Protocol Buffers for use internally and has made protocol compilers for C++, Java and Python available to the public under a free software, open source license. Various other language implementations are also available, including C#, JavaScript, Go, Perl, PHP, Ruby, and Scala.[1]The design goals for Protocol Buffers emphasized simplicity and performance. In particular, it was designed to be smaller and faster than XML.[2] Third parties have reported that Protocol Buffers outperforms the standardized Abstract Syntax Notation One with respect to both message size and decoding performance.[3]Protocol Buffers is widely used at Google for storing and interchanging all kinds of structured information. The method serves as a basis for a custom remote procedure call (RPC) system that is used for nearly all inter-machine communication at Google.[4]Protocol Buffers is very similar to the Apache Thrift protocol (used by Facebook for example), except that the public Protocol Buffers implementation does not include a concrete RPC protocol stack to use for defined services.A software developer defines data structures (called messages) and services in a proto definition file (.proto) and compiles it with protoc. This compilation generates code that can be invoked by a sender or recipient of these data structures. For example, example.proto will produce example.pb.cc and example.pb.h, which will define C++ classes for each message and service that example.proto defines.Canonically, messages are serialized into a binary wire format which is compact, forwards-compatible, and backwards-compatible, but not self-describing (that is, there is no way to tell the names, meaning, or full datatypes of fields without an external specification). There is no defined way to include or refer to such an external specification (schema) within a Protocol Buffers file. The officially supported implementation includes an ASCII serialization format,[5] but this format â though self-describing â loses the forwards-and-backwards-compatibility behavior, and is thus not a good choice for applications other than debugging.Though the primary purpose of Protocol Buffers is to facilitate network communication, its simplicity and speed make Protocol Buffers an alternative to data-centric C++ classes and structs, especially where interoperability with other languages or systems might be needed in the future.A schema for a particular use of protocol buffers associates data types with field names, using integers to identify each field. (The protocol buffer data contains only the numbers, not the field names, providing some bandwidth / storage savings compared with systems that include the field names in the data.)//polyline.protomessage Point {  required int32 x = 1;    required int32 y = 2;   optional string label = 3;   }      message Line {     required Point start = 1;    required Point end = 2;      optional string label = 3;   }      message Polyline {     repeated Point point = 1;       optional string label = 2;    }    The "Point" message defines two mandatory data items, x and y. The data item label is optional. Each data item has a tag. The tag is defined after the equal sign. For example, x has the tag 1.        The Line and "Polyline" messages, which both use Point, demonstrate how composition works in Protocol Buffers. Polyline has a repeated field, which behaves like a vector.        This schema can subsequently be compiled for use by one or more programming languages. Google provides a compiler called protoc which can produce output for C++, Java or Python. Other schema compilers are available from other sources to create language-dependent output for over 20 other languages.[6]        For example, after a C++ version of the protocol buffer schema above is produced, a C++ source code file, polyline.cpp, can use the message objects as follows:        // polyline.cpp#include polyline.pb.h  // generated by calling protoc polyline.proto        Line* createNewLine(const std::string& name) {      // create a line from (10, 20) to (30, 40)        Line* line = new Line;       line->mutable_start()->set_x(10);         line->mutable_start()->set_y(20);        line->mutable_end()->set_x(30);          line->mutable_end()->set_y(40);         line->set_label(name);           return line;        }                Polyline* createNewPolyline() {          // create a polyline with points at (10,10) and (20,20)            Polyline* polyline = new Polyline;           Point* point1 = polyline->add_point();             point1->set_x(10);            point1->set_y(10);              Point* point2 = polyline->add_point();             point2->set_x(20);               point2->set_y(20);              return polyline;              }

当 NVARCHAR(1000) 时，infoData 值:总长度 - 1003

Ä(Á(¼(Protocol Buffers is a method of serializing structured data. It is useful in developing programs to communicate with each other over a wire or for storing data. The method involves an interface description language that describes the structure of some data and a program that generates source code from that description for generating or parsing a stream of bytes that represents the structured data.Google developed Protocol Buffers for use internally and has made protocol compilers for C++, Java and Python available to the public under a free software, open source license. Various other language implementations are also available, including C#, JavaScript, Go, Perl, PHP, Ruby, and Scala.[1]The design goals for Protocol Buffers emphasized simplicity and performance. In particular, it was designed to be smaller and faster than XML.[2] Third parties have reported that Protocol Buffers outperforms the standardized Abstract Syntax Notation One with respect to both message size and dec

当 NVARCHAR(4000) 时，infoData:总长度 - 4084

Ä(Á(¼(Protocol Buffers is a method of serializing structured data. It is useful in developing programs to communicate with each other over a wire or for storing data. The method involves an interface description language that describes the structure of some data and a program that generates source code from that description for generating or parsing a stream of bytes that represents the structured data.Google developed Protocol Buffers for use internally and has made protocol compilers for C++, Java and Python available to the public under a free software, open source license. Various other language implementations are also available, including C#, JavaScript, Go, Perl, PHP, Ruby, and Scala.[1]The design goals for Protocol Buffers emphasized simplicity and performance. In particular, it was designed to be smaller and faster than XML.[2] Third parties have reported that Protocol Buffers outperforms the standardized Abstract Syntax Notation One with respect to both message size and decoding performance.[3]Protocol Buffers is widely used at Google for storing and interchanging all kinds of structured information. The method serves as a basis for a custom remote procedure call (RPC) system that is used for nearly all inter-machine communication at Google.[4]Protocol Buffers is very similar to the Apache Thrift protocol (used by Facebook for example), except that the public Protocol Buffers implementation does not include a concrete RPC protocol stack to use for defined services.A software developer defines data structures (called messages) and services in a proto definition file (.proto) and compiles it with protoc. This compilation generates code that can be invoked by a sender or recipient of these data structures. For example, example.proto will produce example.pb.cc and example.pb.h, which will define C++ classes for each message and service that example.proto defines.Canonically, messages are serialized into a binary wire format which is compact, forwards-compatible, and backwards-compatible, but not self-describing (that is, there is no way to tell the names, meaning, or full datatypes of fields without an external specification). There is no defined way to include or refer to such an external specification (schema) within a Protocol Buffers file. The officially supported implementation includes an ASCII serialization format,[5] but this format â though self-describing â loses the forwards-and-backwards-compatibility behavior, and is thus not a good choice for applications other than debugging.Though the primary purpose of Protocol Buffers is to facilitate network communication, its simplicity and speed make Protocol Buffers an alternative to data-centric C++ classes and structs, especially where interoperability with other languages or systems might be needed in the future.A schema for a particular use of protocol buffers associates data types with field names, using integers to identify each field. (The protocol buffer data contains only the numbers, not the field names, providing some bandwidth / storage savings compared with systems that include the field names in the data.)//polyline.protomessage Point {  required int32 x = 1;    required int32 y = 2;   optional string label = 3;   }      message Line {     required Point start = 1;    required Point end = 2;      optional string label = 3;   }      message Polyline {     repeated Point point = 1;       optional string label = 2;    }    The "Point" message defines two mandatory data items, x and y. The data item label is optional. Each data item has a tag. The tag is defined af

当 NVARCHAR(MAX) 时:具有相同的信息数据输入执行命令后，

conncmd.Execute(); // after this statement 

它会抛出一个错误，比如

At least one parameter contained a type that was not supported.

从错误中可以很清楚地了解到，将不再支持此类型。此外，在 SQL Server Management Studio 中显式执行存储过程时。它工作正常，获得了完整的信息数据，没有任何截断。

USE [TestDB]
GO

DECLARE @return_value int,
        @Id bigint,
        @infoData nvarchar(max),
        @infoStatus tinyint

EXEC    @return_value = "DisplayInfo"
        @channel = N'telephoneMessage',
        @infoType = 1,
        @locationId = N'F6C8B935',
        @Id = @Id OUTPUT,
        @infoData = @infoData OUTPUT,
        @infoStatus = @infoStatus OUTPUT

SELECT  @Id as N'@PayloadId',
        @infoData as N'@MessageData',
        @infoStatus as N'@Status'

SELECT  'Return Value' = @return_value

GO

我还注意到 What is the maximum characters for the NVARCHAR(MAX)?这就是说“NVARCHAR(MAX) 类型的列的最大大小是 2 GByte 的存储空间”。但我当时不明白为什么在这种情况下它会将 NVARCHAR(MAX) 显示为类型不受支持。我已经提到了我正在使用的 SSMS 版本，这样它可能有助于准确修复错误。

SQL 服务器管理工​​作室 2008 R2。 V 10.50.2550.0:SQLAPI++ - 3.8.3

帮助我获取完整的 info_Data，没有任何丢失或截断。

提前致谢。

Answer 1

事实是，您不能在一个查询变量中包含 2GB 的信息。

查询中 NVARCHAR(MAX) 类型列的最大大小为 4000 个字符(1 个 LOB 页)，即使在 SSMS 查询窗口中也是如此 ;-)。

所以，您根本不需要使用那个 (MAX)。

不同的情况是，当表中的列为 NVARCHAR(MAX) 时。在那种情况下，您最多可以在其中保存 2GB 的信息，这些信息保存在多个 LOB 页面中。

更多信息 https://social.msdn.microsoft.com/Forums/sqlserver/en-US/d5e0c6e5-8e44-4ad5-9591-20dc0ac7a870/nvarcharmax?forum=transactsql