none
stored procedure input parameter get truncated

    Question

  •  

    I have a stored procedure that takes an input string parameter defined as @name nvarchar(24).  The stored procedure takes this string and insert it into a table with the column also defined as nvarchar(24).  When I execute this stored procedure with a string of more than 24 characters, the input string somehow get truncated and inserted successfully into the table without giving an error.  Why is this the case?

     

    If I simply execute an insert statement with a string longer than 24 characters, it will give me an error message.  I tried enclosing the insert statement with a try-catch block in the stored procedure but I still can't trap any error. 

     

    Any suggestions?

    Wednesday, January 23, 2008 6:27 PM

Answers

  • You could change the parameter to 25 characters and then test the length for greater than 24 characters.
    Wednesday, January 23, 2008 7:18 PM
  • I was a bit too quick to fire that off.  The only work around is to change the length to 25 and use the IF BEGIN to capture the length > 24.

    Wednesday, January 23, 2008 7:18 PM
    Moderator

All replies

  •  

    run this to understands what happens

     

    Code Snippet

    declare @name nvarchar(24)
    select @name = '1111111111222222222233333333334444444444' 


    select @name

     

     

     

    you need to sanitize the input and check if the string is longer than 24 characters and then disallow that

     

     

    Denis The SQL Menace

    http://sqlservercode.blogspot.com

    http://sqlblog.com/blogs/denis_gobo/default.aspx

    Wednesday, January 23, 2008 6:32 PM
    Moderator
  • The parameter will not error if you provide an input value with more characters than permitted it simply truncates the input value and executes the body of the proc.  The table will not accept the value since the value would be truncated.

     

    Wednesday, January 23, 2008 6:32 PM
    Moderator
  • create PROCEDURE test

    (

    @NAME nvarchar(24)

    )

    As

    BEGIN

    SET NOCOUNT ON

    INSERT INTO test_table (NAME) VALUES (@NAME)

    END

     

     

    EXEC test @NAME = N'123456789012345678901234567890'

     

    Based on your replies, I assume only the first 24 characters of the input string will be stored in @name, that's why the insert statement succeeds.

    I assume this is the default behaviour?  Is there a way to make it fail? 

    If the input string is too long to be stored in the variable, it only make sense to report an error instead of silently truncating it and proceeding.

     

    Wednesday, January 23, 2008 7:07 PM
  • Within IF BEGIN check the length of the parameter and raise an error if it is greater than 24 characters.

     

    Code Snippet

    create PROCEDURE test

    (

    @NAME nvarchar(24)

    )

    As

    IF

    LEN(@NAME) > 24

    BEGIN

    RAISERROR (50005, -- Message id.

    10, -- Severity,

    1, -- State,

    N'parameter length too long');

    END

    ELSE

     

     

    Wednesday, January 23, 2008 7:12 PM
    Moderator
  • It won't work as the string will be stored in @name truncated already, therefore the length will be 24.

     

    Wednesday, January 23, 2008 7:16 PM
  • You could change the parameter to 25 characters and then test the length for greater than 24 characters.
    Wednesday, January 23, 2008 7:18 PM
  • I was a bit too quick to fire that off.  The only work around is to change the length to 25 and use the IF BEGIN to capture the length > 24.

    Wednesday, January 23, 2008 7:18 PM
    Moderator
  • I guess that's the only way out if there aren't any better solution.  Thanks.

     

    Wednesday, January 23, 2008 7:59 PM