SQL Server - updating Varchar(max) column using stored procedure

Question

I have table containing column Email which is varchar(MAX) and I am trying to update it using stored procedure. When the string length is greater than 8000 characters, I get this error

String or binary data would be truncated. The statement has been terminated.

Stored procedure:

ALTER PROCEDURE [dbo].[SaveData]
    @Id BIGINT,
    @Email varchar(max)
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE tb_data
    SET Email = @Email
    WHERE Id = @Id  
END

Please note that datatype for email is set to varchar(MAX)

Interesting thing is when I try to run the update query manually, it works!

I've done this several times but cannot figure out why it's not working in this case.

if the column in table is varchar(MAX), why stored proc parameter is nvarchar(max)? — Harsh, Dec 02 '15 at 06:05
I was wondering why the email address is greater than 8000 characters ? , this link can help you http://www.sqlservercentral.com/Forums/Topic647815-145-1.aspx — japzdivino, Dec 02 '15 at 06:05
use the sql profile to find the actual data passing from your app. — Chandrasekar Kesavan, Dec 02 '15 at 06:08
@JapzDivino: This is column will save the email templates not email id. — Sangram Nandkhile, Dec 02 '15 at 06:12
@Chandru: i've added print statement to check what data is being passed and the data is truncated after 8000th character but it is possible that actual data could be more than what print shows. I think i better use profiler — Sangram Nandkhile, Dec 02 '15 at 06:25
@Sangram try changing it as nvarchar(max) in column and also in store proc — Chandrasekar Kesavan, Dec 02 '15 at 06:32
Is this the complete stored proc? Are you sure you are not assigning value of @email parameter somewhere within the stored proc to some varchar variable and in the process truncating beyond 8000 characters? — Harsh, Dec 02 '15 at 06:36

score -1 · Answer 1 · edited May 23 '17 at 12:07

-1

Hope this can help you:

Microsoft MSDN

Maximum Capacity Specifications for SQL Server

1 byte = 1 character >> 2^31-1 characters

varchar [ ( n | max ) ]

Variable-length, non-Unicode string data. n defines the string length and can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size is the actual length of the data entered + 2 bytes. The ISO synonyms for varchar are char varying or character varying.

How many characters in varchar(max)

Maximum size of a varchar(max) variable

edited May 23 '17 at 12:07

Community

1
1

answered Dec 02 '15 at 06:09

japzdivino

1,736
3
17
25

-1: From the question,it seems like OP does understand what he is doing and need help for particular scenario. Posting links to MSDN articles will not help in such cases. – Sangram Nandkhile Dec 02 '15 at 06:50
@user2564977 I respect your opinion , i'm just trying to help :) Regards – japzdivino Dec 02 '15 at 07:45

SQL Server - updating Varchar(max) column using stored procedure

1 Answers1