I have a stored procedure that takes an input string parameter defined as @name nvarchar(24). The stored procedure takes this string and insert it into a table with the column also defined as nvarchar(24). When I execute this stored procedure with a string of more than 24 characters, the input string somehow get truncated and inserted successfully into the table without giving an error. Why is this the case?
If I simply execute an insert statement with a string longer than 24 characters, it will give me an error message. I tried enclosing the insert statement with a try-catch block in the stored procedure but I still can't trap any error.
run this to understands what happensCode Snippet
declare @name nvarchar(24)
select @name = '1111111111222222222233333333334444444444'
you need to sanitize the input and check if the string is longer than 24 characters and then disallow that
Denis The SQL Menacehttp://sqlservercode.blogspot.com http://sqlblog.com/blogs/denis_gobo/default.aspx
create PROCEDURE test
SETNOCOUNT ON INSERT INTO test_table (NAME) VALUES (@NAME)
EXEC test @NAME = N'123456789012345678901234567890'
Based on your replies, I assume only the first 24 characters of the input string will be stored in @name, that's why the insert statement succeeds.
I assume this is the default behaviour? Is there a way to make it fail?
If the input string is too long to be stored in the variable, it only make sense to report an error instead of silently truncating it and proceeding.
Within IF BEGIN check the length of the parameter and raise an error if it is greater than 24 characters.Code Snippet
LEN(@NAME) > 24
RAISERROR(50005, -- Message id.
10, -- Severity,
1, -- State,
N'parameter length too long');