StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POException executing a stored procedure with CASE switching from C# (T-SQL)
text
Body
copied!<p>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:</p> <pre><code>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 </code></pre> <p>All is good if I execute this procedure from SQL Management Studio with </p> <pre><code>EXEC [dbo].[updateTable] @columnName = 'BigNvarcharValue', @value = N'SOME BIG 80Kb value' </code></pre> <p>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:</p> <pre><code>CREATE PROCEDURE updateTable @columnName sysname, @value nvarchar(max) AS UPDATE [dbo].[TestTable] SET BigNvarcharValue=@value </code></pre> <p>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.</p> <p>I'm fairly new to T-SQL, so are there any limitations of CASE that I dont know of?</p> <p>P.S. BigNvarcharValue is NVARCHAR(MAX) and TableName is NVARCHAR(50)</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload