debugMinor
Is there a setting to force an error when asigning a varchar longer than a variable?
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:
Inserting a varchar into a smaller Table column
yields an error:
This truncates silently:
declare @code varchar(10)
set @code = 'This is too long'
select @codeInserting 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 @codeCode 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 @codeContext
StackExchange Database Administrators Q#14185, answer score: 4
Revisions (0)
No revisions yet.