What SqlDbType maps to varChar(max)?

Question

What SqlDbType maps to varChar(max)? SqlDbType.VarChar says that it is limited to 8K, though that may be a documentation error.

Currently I see 3 different answers, which solution did you actually use? — edosoft, May 10 '10 at 19:26
In the end I found that not explicitly setting the SqlDbType was the best way to go. It is far less error prone and doesn't have to be changed when the stored proc is changed. — Jonathan Allen, May 10 '10 at 23:55
But now your code is building an execution plan every time a different length string is passed to the SP. It's not optimal. MSDN shows to use -1, as eglasius states below. — HardCode, Oct 25 '10 at 17:52

score 24 · Accepted Answer · answered Feb 27 '09 at 20:10

24

Actually you can use it with VarChar. If it is a parameter you can build it with: new SqlParameter("@myparam", SqlDbType.NVarChar, -1)

See: http://msdn.microsoft.com/en-us/library/bb399384.aspx

answered Feb 27 '09 at 20:10

eglasius

35,831
5
65
110

1

Do I have to pass a size of -1 or can I just leave it blank? – Jonathan Allen Feb 28 '09 at 00:55
1

related question http://stackoverflow.com/questions/973260/what-size-do-you-use-for-varcharmax-in-your-parameter-declaration – TheEmirOfGroofunkistan Jan 23 '13 at 20:42

score 0 · Answer 2 · answered Mar 19 '13 at 17:54

OK... Finally figured this out... if you get an error stating : " String[0]: the Size property has an invalid size of 0." You merely have to set the size parameter of the output SQL Parameter as so:

Dim parameter As SqlParameter = New SqlParameter("@FinalImportCount", SqlDbType.NVarChar)
parameter.Direction = ParameterDirection.Output
parameter.Size = 4000
cmd.Parameters.Add(parameter)

Hope this helps someone -- been trying to figure this out for 2 hours.... grrrrr.

score -1 · Answer 3 · answered Feb 28 '09 at 12:06

-1

Use VarChar, and set size to 2147483647 (2gb-1)

answered Feb 28 '09 at 12:06

Frans Bouma

8,259
1
27
28

What SqlDbType maps to varChar(max)?

3 Answers3