HiveBrain v1.2.0
Get Started
← Back to all entries
debugMinor

Is there a setting to force an error when asigning a varchar longer than a variable?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
errorforcelongerthanvarcharsettingwhenasigningtherevariable

Problem

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.

Solution

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

ELSE
BEGIN
    SELECT 'Inputted string is too long for the variable.'
    RETURN
END

select @code

Code Snippets

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

ELSE
BEGIN
    SELECT 'Inputted string is too long for the variable.'
    RETURN
END

select @code

Context

StackExchange Database Administrators Q#14185, answer score: 4

Revisions (0)

No revisions yet.