How do I force nvarchar input width in SQL Server?

Question

I’ve got a script that I’ve created for our production line where the user enters some variables into the script before executing. The Problem is that the variables are NVARCHAR(9) and if the user inputs a 10 character sting the last character is cut off (as expected) what I want to know is how can I have SQL throw an error if they enter a value that is too long? This issue stems from users fat fingering their inputs. Example:

Valid input - 
DECLARE @ClientCode NVARCHAR(9)
SET @ClientCode = N'ABCDEFGHI'
SELECT  @ClientCode

Results
ABCDEFGHI

Invalid input –

DECLARE @ClientCode NVARCHAR(9)
SET @ClientCode = 'ABCDDEFGHI'
SELECT @ClientCode

Results
ABCDDEFGH

What I’m hoping for is a setting that will have SSMS raise an error. What I’m hoping to avoid is something like -

DECLARE @ClientCode NVARCHAR(50)
...

IF LEN(@ClientCode) > 9
RAISERROR('Too long dummy.',16,1)

Thanks for you help

Answer 1

Please see SQL Server silently truncates varchar's in stored procedures - SQL Server cannot be set to automatically raise truncation errors for inserts inside of a stored procedure, so you have to perform the check yourself. You can do this in the stored procedure (the code you listed as "hoping to avoid") or you can validate beforehand in the client application.