Exception executing a stored procedure with CASE switching from C# (T-SQL)

Question

I have a NVARCHAR(max) column in a table and a stored procedure that would update this column as well as any other column in the table using CASE switching:

CREATE PROCEDURE updateTable
   @columnName sysname,
   @value nvarchar(max)
AS 
   UPDATE [dbo].[TestTable]
   SET 
      BigNvarcharValue = CASE @columnName WHEN 'BigNvarcharValue' THEN @value ELSE BigNvarcharValue END,  
      TableName = CASE @columnName WHEN 'TableName' THEN @value ELSE TableName END

All is good if I execute this procedure from SQL Management Studio with

EXEC [dbo].[updateTable]
     @columnName = 'BigNvarcharValue',
     @value = N'SOME BIG 80Kb value'

I can also update TableName from C# code using the same stored procedure, but when it comes to updating this BigNvarcharValue from C#, it fails with SQLException that "String or binary data would be truncated". Now, I figured it has something to do with CASE in this stored procedure, because when I break it to a simpler stored procedure, everything works fine:

CREATE PROCEDURE updateTable   
     @columnName sysname,   
     @value nvarchar(max)
AS       
   UPDATE [dbo].[TestTable]   
   SET BigNvarcharValue=@value

I read a bunch of forum posts that describe this problem of trying to insert a bigger value into NVARCHAR column that would cause this exception, but it doesnt seem to apply.

I'm fairly new to T-SQL, so are there any limitations of CASE that I dont know of?

P.S. BigNvarcharValue is NVARCHAR(MAX) and TableName is NVARCHAR(50)

Answer 1

The error says it by itself, "String or binary data would be truncated". This means that you seem to insert a larger value than what the nvarchar(max) can handle.

SSMS 2008 has some debugging features allowing to set breakpoints, etc.

I think you might wish to take an eye out to the System.String maximum capacity either. This is only a matter of length, somewhere.

Answer 2

What are the data types of the columns you're dealing with? Because I've reproduced the error by attempting to insert a value that is allowed by NVARCHAR(max) into a column that is VARCHAR(50).

To reiterate - NVARCHAR(max) is allowing you to specify a value that is longer than the stated data type, which is why you get the error about truncation.

Answer 3

With your exact same stored procedure and the table you described I ran the following code

class Program
    {
    static void Main(string[] args)
    {

        using(SqlConnection cnn = new SqlConnection(@"Server=.;Database=test;Trusted_Connection=True;"))
        {
            cnn.Open();
            SqlCommand cmd = new SqlCommand("updateTable",cnn);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@columnName",
                   System.Data.SqlDbType.NVarChar, 128));

            cmd.Parameters["@columnName"].Value = "BigNvarcharValue";

            cmd.Parameters.Add(new SqlParameter("@value",  
                     System.Data.SqlDbType.NVarChar, -1));
            cmd.Parameters["@value"].Value = new string('T', 80000);

            cmd.ExecuteNonQuery();



        }

    }
}

It worked fine. I would inspect the command text and the parameter collection (name and value) and verify every is as you think it is.

Answer 4

Thanks everyone for the responses. I ended up separating update of the big column to an individual procedure, which solved the problem. Im sure the culprit was with the CASE statement.