Post reply

Varchar truncation

  • September 10, 2003 at 4:02 pm

    #101661

    Hello everyone,

    I have been caught off guard with this problem that SQL Server automatically truncates a string that exceeds its declaration.

    Simplifying the problem, it would come to this:

    DECLARE @test-2 varchar(3)

    SET @test-2='More than 3'

    PRINT @test-2

    @test-2 will contain the string 'Mor' and SQL Server does not give an error message.

    Do you know a work around so that the script will return an error if the length of the string is greater than the variable length?

    In Oracle, it is like:

    1 DECLARE v_test VARCHAR(3);

    2 BEGIN

    3 v_test:='More than 3';

    4* END;

    SQL> /

    DECLARE v_test VARCHAR(3);

    *

    ERROR at line 1:

    ORA-06502: PL/SQL: numeric or value error: character string buffer too small

    ORA-06512: at line 3

    Don't get me wrong, I like SQL Server than Oracle, but this feature has cost me quite a few wrinkles in our stored procedures... We both support SQL Server and Oracle...

    Thanks for your help.

  • September 10, 2003 at 4:54 pm

    #473566

    You should be getting the following error:

    "Server: Msg 8152, Level 16, State 9, Line 1

    String or binary data would be truncated.

    The statement has been terminated."

    Are you running your statement from an application or some other db connection than straight from Query Analyzer?

    Curious. Thanks.

    David

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • September 10, 2003 at 5:21 pm

    #473576

    DavidB,

    Running from QA I get the same results as Jags2001. It's only if I try to manipulate (insert or update) a table column declared similarly that I get the error you noted.

    Interested to know if you get the error just running Jags2001's code.

    Cheers,

    - Mark


    Cheers,
    - Mark

  • September 10, 2003 at 5:39 pm

    #473582

    Does not answer the question.

    SET ANSI_WARNINGS OFF

    go

    DECLARE @test varchar(3)

    SET @test='More than 3'

    PRINT @test

    Go

    Create table Test(t varchar(3))

    Go

    Insert Test values('12345')

    Go

    Drop table test

    Go

    SET ANSI_WARNINGS ON

    go

    DECLARE @test varchar(3)

    SET @test='More than 3'

    PRINT @test

    Go

    Create table Test(t varchar(3))

    Go

    Insert Test values('12345')

    Go

    Drop table test

    Go

  • September 10, 2003 at 6:09 pm

    #473591

    This only happens with variables. When you try and put too many characters into a field in a table you get a truncation error.

    Don't know any workarounf for variables. You would actually need to know the length beforehand to do the test before the assignment.

    The example you provided doesn't really illustrate the problem, can you provide an actual example where this is an issue?

    Hope this helps

    Phill Carter

    --------------------

    Colt 45 - the original point and click interface

    --------------------
    Colt 45 - the original point and click interface

  • September 10, 2003 at 6:21 pm

    #473593

    Sorry - Brain dead. You are correct. The error I generated was from an insert. Phillcart is right on with his response. Curious to see the example you provide and what can be done as a workaround.

    David

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • September 11, 2003 at 9:07 am

    #473743

    Thanks for all your replies. I use QA to run the test. I have a lot of SP's of this sort. I just realized this one day when the SP's happilly processed their overly long inputs and then someone pointed to me that something is not working in the app. We realized that the strings were truncated.

    My only workaround is to over declare the variables and then check whether the length exceeds the required length.

    DECLARE @test-2 varchar(20)

    SET @test-2='More than 3'

    IF Len(@test)> 3

    BEGIN

    RAISERROR ('The string you passed is greater than 3',16,1)

    RETURN

    END

    -- Proceed only if the variables do not exceed the requirement.

    -- I could create another function or SP to handle this.

    --

    PRINT @test-2

    It is awful but I don't think I can get MS to change this behavior anytime soon. We will have to change the SQL Server SP's (but not Oracle)

    Any other ideas?

    Thanks again...

  • September 11, 2003 at 9:34 am

    #473749

    hi,

    if you are on SQL 2000 you could declare the variable as a table datatype, and when you assign a string that it too long it returns the appropriate error :-

    declare @string table (col1 varchar(3))

    insert into @string values ("too long")

    returns :-

    Server: Msg 8152, Level 16, State 9, Line 2

    String or binary data would be truncated.

    The statement has been terminated.

    HTH

    Paul

  • September 11, 2003 at 10:05 am

    #473752

    Hi Paul,

    Thanks for your idea. That might work...

    However, I would be interested in col1 and not @string in your example.

    declare @string table (col1 varchar(3))

    declare @test-2 varchar(3)

    insert into @string values ('too long')

    If @@error >0 RETURN

    SELECT @test-2 = col1 from @string

    PRINT @test-2

    I'm not sure if this is more efficient than my work around.

    Thanks again.

    Jags

  • September 11, 2003 at 10:22 am

    #473756

    jags,

    I see what you mean, it's not easy !

    Paul

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply