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

I think you should put this validation client-side instead of in the database... don't need to make a round trip to the database just to tell the user that the input was too long. Better yet, don't even let them enter a string that is too long. — Michael Fredrickson, Dec 14 '12 at 16:04
I agree with you. However that isn’t an option in the system I’m working on. These scripts are being run by very JR techs from within SSMS. So there is no client-side. This script is used to create the client-side of things. My solution would be to have all of these scripts be fired off by a web front end that the techs can log into, enter their parameters into a web form, and then click go. Unfortunately management wants to keep with the status quo of “We’ve been doing it this way for years I don’t understand why we should spend the money to do it the right way”… — Donovan Jones, Dec 14 '12 at 16:17

score 1 · Accepted Answer · edited May 23 '17 at 10:24

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.

edited May 23 '17 at 10:24

Community

1
1

answered Dec 14 '12 at 16:11

Scott Chapman

920
1
7
13

Thanks Scott, That's what I thought the answer would be. I spent some time looking through question here but didn’t come across this one. . – Donovan Jones Dec 14 '12 at 16:34

score 0 · Answer 2 · answered Dec 14 '12 at 16:19

0

I've also been looking at the question Scott Chapman references in his answer, however, I found igorp's answer midway down interesting, I had to hack it a bit to fix the SQL but it can work:

declare @p1 varchar(max), @p2 varchar(max)
select @p1 = 'abcd'
declare @p1Int varchar(2), @p2Int varchar(3)
declare @test table (p1 varchar(2), p2 varchar(3))
insert into @test (p1,p2) values (@p1, @p2)
select @p1Int=p1, @p2Int=p2 from @test

answered Dec 14 '12 at 16:19

Barry Kaye

7,682
6
42
64

That is an interesting solution. It is however just as cumbersome as DECLARE @ClientCode NVARCHAR(50) ... IF LEN(@ClientCode) > 9 RAISERROR('Too long dummy.',16,1) – Donovan Jones Dec 14 '12 at 16:39

How do I force nvarchar input width in SQL Server?

2 Answers2