I would like to get an error when assigning a longer varchar to a variable and no silent truncation. Are there any settings?

This truncates silently:

declare @code varchar(10)
set @code = 'This is too long'
select @code

Inserting a varchar into a smaller Table column

create table test (col1 varchar(10));
insert into test values ('This is too long');

yields an error:

Meldung 8152, Ebene 16, Status 14, Zeile 2
String or binary data would be truncated.
The statement has been terminated.
  • I wish there were. This bites me in the ass all the time, particularly with stored procedure parameters.
    – db2
    Mar 1, 2012 at 13:15
  • Related : stackoverflow.com/questions/4628140/…
    – JNK
    Mar 1, 2012 at 14:30
  • unfortunately the jist is, there's no automated way to check for this.
    – JNK
    Mar 1, 2012 at 14:33

2 Answers 2


This should work for this problem:

declare @code varchar(10)
declare @input VARCHAR(200)
set @input = 'Set your input code here'

IF(LEN(@input) <= 10) -- make sure this number is same as length of 'code' variable
    SET @code = @input

    SELECT 'Inputted string is too long for the variable.'

select @code
  • 1
    I do not want to change other peoples scripts, I want to to force the system to trow errors when I have to run the script.
    – bernd_k
    Mar 1, 2012 at 12:28
  • Silent truncation doesn't raise a SQL Server error (hence the name) so you will have to do the error handling within the code. Mar 1, 2012 at 15:22

One solution would be to
1 change all incoming parameters to be varchar(max)
2. have sp private variable of the correct datalength (simply copy and paste all in parameters and add "int" at the end
3. declare a table variable with the column names the same as variable names
4. Insert into the table a row where each variable goes into the column with the same name
5. Select from the table into internal variables

This way your modifications to the existing code are going to be very minimal like in the sample below
Original code
create procedure spTest
@p1 varchar(2),
@p2 varchar(3)
new code
create procedure spTest
@p1 varchar(max),
@p2 varchar(max)
declare @p1Int varchar(2), @p2Int varchar(3)
declare @test table (p1 varchar(2), p2 varchar(3)
insert into @test (p1,p2) varlues (@p1, @p2)
select @p1Int=p1, @p2Int=p2 from @test
Note that if the length of the incoming parameters is going to be greater than the limit instead of silently chopping off the string SQL Server will throw off an error.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.