SQL Performance Large Variable Varchar into Small Column
Is performance really your main concern here? Or are you just looking for an excuse to not bother making sure the parameter data types match the column data types? (This happens to be a common reason for just declaring everything as nvarchar(max).)

Your parameters should absolutely match the underlying column types. If one changes, you change both.

Declaring larger data types for variables that will never hold that much data is probably wasteful, performance-wise, since memory has to be reserved in case you update the variable later. But this performance difference (if it even exists) is completely irrelevant, IMHO.

The reason? If someone puts 26 characters into the first variable, the insert will fail. And the error message is useless:


  Msg 8152, Level 16, State 14
  
  String or binary data would be truncated.
  The statement has been terminated.


Imagine you have 10 columns of varying lengths, and 10 varchar(4000) parameters, and then you call this stored procedure 100 times, and get the error 20 times? Happy hunting!

Why invite errors that are preventable (or that can at least happen further up the chain)? You should be validating your input all the way up the stack:


Make sure the form field can't be more than 25 characters (HTML supports this);
make sure the client-side validation check that it is not more than 25 characters (JavaScript supports this);
make sure the .NET data type is declared as 25 characters; and, for the love of all things you deem holy,
make that parameter 25 characters.


Yes, that makes for more work when the length changes, but anything else is inexcusable IMHO.