Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.
share|improve this question
    
I wish there were. This bites me in the ass all the time, particularly with stored procedure parameters. –  db2 Mar 1 '12 at 13:15
    
Related : stackoverflow.com/questions/4628140/… –  JNK Mar 1 '12 at 14:30
    
unfortunately the jist is, there's no automated way to check for this. –  JNK Mar 1 '12 at 14:33
add comment

2 Answers

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
share|improve this answer
    
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 '12 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. –  Vince Pergolizzi Mar 1 '12 at 15:22
add comment

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.

share|improve this answer
    
Beware there is a slight performance penalty to using the MAX datatypes. –  Nick Chammas Apr 19 '12 at 21:39
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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