1

When adding to a parameter collection in .NET C# using SqlParamaterCollection.Add, an overloaded method determines the size of the parameter.

Another overloaded method does not include the size of the parameter, but the example of its use shows the size being set immediately after the method is used.

In both cases, the documentation doesn't specify how to max the parameter size a "MAX" value. For example, as when inserting into a column of type NVARCHAR(MAX).

How should this be done?

// ? below indicates confusion on how to make this MAX.
// it only takes type "int"
var parameter = new SqlParameter("@param1", SqlDbType.NVarChar, ?);
command.Parameters.Add(parameter);
Aaron Thomas
  • 5,054
  • 8
  • 43
  • 89
  • Interesting question, but why would you need to supply `MAX`? Why wouldn't you get the string's length and use it? – CodeMonkey1313 Mar 16 '17 at 17:24
  • If direction is not output you don't need to set size. – Crowcoder Mar 16 '17 at 17:24
  • 2
    You use -1. [Example](http://stackoverflow.com/questions/21087950/how-to-create-nvarcharmax-sqlparameter-in-c) and [Another Example](http://stackoverflow.com/questions/973260/what-size-do-you-use-for-varcharmax-in-your-parameter-declaration) – Blake Thingstad Mar 16 '17 at 17:32

1 Answers1

0

Just use VARCHAR or NVARCHAR without specifying the size, which is default to MAX, but should not exceed 8000 for VARCHAR and 4000 for NVARCHAR

LONG
  • 4,490
  • 2
  • 17
  • 35
  • can you provide documentation for those limits? i don't remember seeing that. – Aaron Thomas Mar 16 '17 at 17:33
  • 1
    https://msdn.microsoft.com/en-us/library/system.data.sqldbtype(v=vs.110).aspx – LONG Mar 16 '17 at 17:34
  • that is the limits in SQL server, try to use `REPLICATE` to insert any string larger than that size, it will throw you the error – LONG Mar 16 '17 at 17:35
  • Actually, MAX indicates the column data is stored out of row while a number (up to 8000 for VARCHAR and 4000 for NVARCHAR) would be stored in row. This is more important for defining table structures than stored procedures. But MAX actually means 2 GB of data could be returned (at least for VARCHAR - https://msdn.microsoft.com/en-us/library/ms176089.aspx). – CodeMonkey1313 Mar 16 '17 at 19:54