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:
我有一个我为生产线创建的脚本,用户在执行前将一些变量输入到脚本中。问题是变量是NVARCHAR(9),如果用户输入10个字符,那么最后一个字符被截断(正如预期的那样)我想知道的是如果输入一个值,我怎么能让SQL抛出一个错误太长?这个问题源于用户指责他们的输入。例:
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 -
我希望的是SSMS会引发错误的设置。我希望避免的是 -
DECLARE @ClientCode NVARCHAR(50)
...
IF LEN(@ClientCode) > 9
RAISERROR('Too long dummy.',16,1)
Thanks for you help
谢谢你的帮助
2 个解决方案
#1
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.
请参阅SQL Server静默截断存储过程中的varchar - SQL Server不能设置为自动引发存储过程内插入的截断错误,因此您必须自己执行检查。您可以在存储过程(您列为“希望避免”的代码)中执行此操作,也可以事先在客户端应用程序中进行验证。
#2
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:
我也一直在研究Scott Chapman在他的回答中引用的问题,然而,我发现igorp的答案中途有点兴趣,我不得不修改它以修复SQL但它可以工作:
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
#1
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.
请参阅SQL Server静默截断存储过程中的varchar - SQL Server不能设置为自动引发存储过程内插入的截断错误,因此您必须自己执行检查。您可以在存储过程(您列为“希望避免”的代码)中执行此操作,也可以事先在客户端应用程序中进行验证。
#2
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:
我也一直在研究Scott Chapman在他的回答中引用的问题,然而,我发现igorp的答案中途有点兴趣,我不得不修改它以修复SQL但它可以工作:
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