CODEWITHSUNDEEP
c#sqlstored-procedurestable-valued-parameters
Arianule
Arianule

Reputation: 9043

Setting nvarchar length to maximum in table valued parameters

I want to pass a table valued parameter as a variable to a stored procedure and in the constructor of class SqlMetadata one can specify the length (long maxLength) of the string one wants to add in the column of the table.

Microsoft.SqlServer.Server.SqlMetaData[] tvpdefinition = 
        {

            new SqlMetaData("ValueOne", SqlDbType.NVarChar, 100),
            new SqlMetaData("ValueTwo",SqlDbType.NVarChar, 100)
        }

How can one go about specifying a 'max' length so that it corresponds with this column 

ValueOne (nvarchar(max), not null)

as opposed to a length value of 100 for example

Upvotes: 19

Views: 6151

Answers (1)

Steve
Steve

Reputation: 216293

In this article on MSDN it is specified that you can set the MAX size in this way

SqlParameter myParam = new SqlParameter("@paramName", SqlDbType.NVarChar, SqlMetaData.Max );

See the last example on the above mentioned article.
So, without knowing exactly how your SqlMetaData class is defined, and supposing that the last parameter is the size propery of an underlying SqlParameter, I think you could write

Microsoft.SqlServer.Server.SqlMetaData[] tvpdefinition = 
{
    new SqlMetaData("ValueOne", SqlDbType.NVarChar, SqlMetaData.Max ),
    ....
}

Upvotes: 27

Related Questions