42

I've got the following code to pull back a DataTable using a stored procedure and inputting a string parameter @JobNumbers, which is dynamically created string of job numbers (and therefore length is unknown):

using (SqlConnection connection = new SqlConnection(con))
        {
            SqlCommand cmd = new SqlCommand("dbo.Mystoredprocedure", connection);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@JobNumbers", SqlDbType.VarChar, 4000);
            cmd.Parameters["@JobNumbers"].Value = JobNumber;

            SqlDataAdapter da = new SqlDataAdapter(cmd);

            connection.Open();
            da.Fill(JobDetails);
        }

As you can see I've currently set the JobNumber parameter to a length of 4000, which should be enough to take around 500 job numbers and should be enough. However, there is the possibility that it may need more on the odd occasion. So, I was wondering, is there a way to set the parameter to the equivalent sql parameter type of nvarchar(max)?

I've had a look at various similar questions (What's the best method to pass parameters to SQLCommand?) but none specifically say whether you can (or can't) do this. Secondly, is it even necessary to do this if I set the @JobNumber parameter in the stored procedure to nvarchar(max) and therefore presumably I wouldn't need to set the length in C# at all? If I do this will this have potential performance issues as suggested in this question When should "SqlDbType" and "size" be used when adding SqlCommand Parameters??

Community
  • 1
  • 1
sr28
  • 4,728
  • 5
  • 36
  • 67
  • 1
    Take a look here: http://stackoverflow.com/questions/973260/what-size-do-you-use-for-varcharmax-in-your-parameter-declaration – Eugene Jan 13 '14 at 09:43
  • It would be better to use a datatype that naturally supports storing multiple separate data items, rather than this string. The two types that leap out as supporting these are tables themselves (in the form of separate rows for each item) or XML. – Damien_The_Unbeliever Jan 13 '14 at 09:54

1 Answers1

97

This is how you explicitly set nvarchar(max):

cmd.Parameters.Add("@JobNumbers", SqlDbType.NVarChar, -1);

If you're really concerned with performance you might want to consider passing a table of integers: https://stackoverflow.com/a/10779593/465509

Community
  • 1
  • 1
Sam7
  • 3,382
  • 2
  • 34
  • 57
  • don't you mean SqlDbType.NVarChar? – Eugene Jan 13 '14 at 10:32
  • Well it depends on what datatype you want. VarChar & NVarChar both work. An nvarchar column can store any Unicode data. A varchar column is restricted to an 8-bit codepage. For more: http://stackoverflow.com/a/147302/465509 – Sam7 Jan 13 '14 at 10:58
  • 2
    You said yourself in the line above that it was nvarchar(max) that was wanted, so why did you then use `SqlDbType.VarChar`? – robertc Mar 02 '15 at 16:45
  • Thanks for noticing. Since the title says NVarchar(max) I'll make it a consistent NVarchar. – Sam7 Mar 02 '15 at 22:31