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
– Michael Fredrickson, Commented Dec 14, 2012 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
– Donovan Jones, Commented Dec 14, 2012 at 16:17

Community · Accepted Answer · 2017-05-23 10:24:58Z

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.

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. .

Barry Kaye · Accepted Answer · 2012-12-14 16:19:08Z

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

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)

Collectives™ on Stack Overflow

How do I force nvarchar input width in SQL Server?

2 Answers 2

1 Comment

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Linked

Related