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: 246977

  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.

  The absence of evidence is not evidence of absence.
  Martin Rees

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel

• Arthur Kirchner

  Ten Centuries

  Points: 1239

  More actions

  May 7, 2024 at 12:00 pm

  #4419342

  Thanks a ton!