Is there a way to prevent silent truncation of SQL Server data in local variables and stored procedure parameters?

Question

I recently encountered a problem when porting an application to SQL Server. It turned out that this problem was caused by the fact that the stored procedure parameter was declared too short for the data transferred to it: the parameter was declared as VARCHAR(100) , but in one case more than 100 data characters were transmitted. I was surprised that SQL Server did not report any errors or warnings - it just silently truncated the data to 100 characters.

The following SQLCMD session demonstrates this:

  1> create procedure WhereHasMyDataGone (@data varchar (5)) as
 2> begin
 3> print 'Your data is' '' + @data + '' '.';
 4> end;
 5> go
 1> exec WhereHasMyDataGone '123456789';
 2> go
 Your data is '12345'.

Local variables also have the same behavior:

  1> declare @s varchar (5) = '123456789';
 2> print @s;
 3> go
 12345

Is there an option in which I can enable SQL Server errors (or at least warnings) in such situations? Or should I just declare all local variables and stored procedure parameters as VARCHAR(MAX) or NVARCHAR(MAX) ?

Answer 1

SQL Server does not have this option. You will have to either manually check the length of the lines in the stored procedure, or somehow process longer lines, or use the nvarchar (max) parameter. If disk space is not a problem, the nvarchar (max) option is by far the easiest and fastest solution.

Answer 2

You do not need to use nvarchar (max), just use nvarchar (length + 1) [for example. if the column length is 50, you must set the parameter nvarchar (51)]. See Reply from DavidHyogo - SQL Server silently truncates varchar in stored procedures .

Answer 3

I do not know how to make a server, but I used the SQL Server Projects feature for Visual Studio Team System Developer Edition. It includes code analysis that has caught my truncation problem: using the int parameter to insert into the smallint column.

Answer 4

You can use LEFT in SQL and specify the length you want to insert. eg.

CREATE TABLE Table1 (test varchar (10))

enter the values ​​of Table1 (LEFT ('abcdefghijklmnopqrstuvwxyz', 10))

It will only be

abcdefghij on the table

Answer 5

Although inconvenient, you can, however, dynamically check the length of a parameter before calling, for example.

 CREATE FUNCTION MyFunction(@MyParameter varchar(10)) RETURNS int AS BEGIN RETURN LEN(@MyParameter) END GO DECLARE @MyValue varchar(15) = '123456789012345' DECLARE @ParameterMaxLength int SELECT @ParameterMaxLength = CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_SCHEMA = 'dbo' AND SPECIFIC_name = 'MyFunction' AND PARAMETER_NAME = '@MyParameter' IF @ParameterMaxLength <> -1 AND LEN(@MyValue) > @ParameterMaxLength PRINT 'It' too looooooooooooooooooong' 

I skipped the database name of the called function in the request and in the link to INFORMATION_SCHEMA.PARAMETERS to make sure that my sample will work unchanged.

I do not necessarily defend this, but I would like to point out that the information may be available to detect imminent truncation dynamically, if necessary in some critical situation.