Stored Procedures - passed string than variable length

• Arthur Kirchner

  Ten Centuries

  Points: 1239

  More actions

  May 7, 2024 at 11:33 am

  Go to Answer

  #4419336

  Dear all,

  I was just wondering: Can you change the default behaviour as illustrated below?

  The user passes a longer string than it was defined in the proc parameter and may not get a feedback, that the string was not processed as he/she wanted. (e.g. when inserting the value into a table)

  create procedure prp
  @x varchar(1)
  as select @x
  exec prp @x='x'  -- => x
  exec prp @x='xy' -- => x

  I'm absolutely not new to MSSQL, but funnily I never thought that this could be a problem 🙂

  Best Regards

  Arthur

   

• Phil Parkin

  SSC Guru

  Points: 247180

  More actions

  May 7, 2024 at 11:50 am

  Answer

  #4419341

  If you search other forums, you will see that this is commonly known as the 'silent truncation issue'. The most common workarounds appear to be as follows:

  1. Do your length validation in whatever app is calling the proc.
  2. Set the length of the varchar column to be 1 character more than what is valid. You can then throw an error (or whatever) within the proc if the supplied parameter is that length.

  ---

• Arthur Kirchner

  Ten Centuries

  Points: 1239

  More actions

  May 7, 2024 at 12:00 pm

  #4419342

  Thanks a ton!