Practically it doesn’t make sense that SQL Server throws an error if we insert a record with longer value string to the column but If that insert happens with stored procedure than stored procedure will silently truncate the parameter value to the specified length value. For example, if I have defined a parameter @Param Varchar (10) and if I pass my Parameter value as ‘SQL is trimmer’ it will truncate all the characters after the 10 character and stored first 10 character in a database table. Let run this as an example below.
Define the Stored Procedure and Database.
–Create Database
Create Database TSQLTest
GO
—Use TSQLTest
USE TSQLTest;
GO
—Create ParameterCheck
Create Table dbo.ParameterCheck
(
Id Int Identity,
Col1 VARCHAR (10) ,
Col2 Varchar (5) ,
ConstraintPrimary Key (Id)
);
GO
—Create Procedure with the parameter with same length of the table.
Create Procedure usp_ParameterCheck_Insert
@Param_Col1 Varchar (10),
@Param_Col2 Varchar (5)
AS
BEGIN
SET NOCount ON;
INSERT dbo.ParameterCheck(Col1, Col2)
SELECT @Param_Col1, @Param_Col2;
SET NOCOUNT OFF;
END;
GO
Now Let’s call the stored procedure and see the result.
—Stored Procedure call by passing parameter value.
EXEC usp_ParameterCheck_Insert
@Param_Col1 = ‘SQL Is Trimmer.’
, @Param_Col2 = ‘Trust Me.’
GO
—Get the Result From the table.
SELECT * From dbo.ParameterCheck
Result:
What are the Alternatives?
Alternative 1: I have read on other forums and people will say allow one character more than the actual length of the column and get exception which return to the application but the error doesn’t say which parameter value or on which column has longer string so this solution isn’t going to work. Please see below screen shot for it.
Alternative 2: The second approach I was trying with Table Valued Parameter as below but it has also the same problem the error doesn’t say which column has longer string as below.
Conclusion:
Max Character length should be at Application Level. It would be idle that client stop typing extract character than allowed length.
SQL Skills 