SQL Server Silently Truncates Varchar's in Stored Procedures

SQL Server silently truncates varchar's in stored procedures

It just is.

I've never noticed a problem though because one of my checks would be to ensure my parameters match my table column lengths. In the client code too. Personally, I'd expect SQL to never see data that is too long. If I did see truncated data, it'd be bleeding obvious what caused it.

If you do feel the need for varchar(max) beware a massive performance issue because of datatype precedence. varchar(max) has higher precedence than varchar(n) (longest is highest). So in this type of query you'll get a scan not a seek and every varchar(100) value is CAST to varchar(max)

UPDATE ...WHERE varchar100column = @varcharmaxvalue

Edit:

There is an open Microsoft Connect item regarding this issue.

And it's probably worthy of inclusion in Erland Sommarkog's Strict settings (and matching Connect item).

Edit 2, after Martins comment:

DECLARE @sql VARCHAR(MAX), @nsql nVARCHAR(MAX);
SELECT @sql = 'B', @nsql = 'B'; 
SELECT 
   LEN(@sql), 
   LEN(@nsql), 
   DATALENGTH(@sql), 
   DATALENGTH(@nsql)
;

DECLARE @t table(c varchar(8000));
INSERT INTO @t values (replicate('A', 7500));

SELECT LEN(c) from @t;
SELECT 
   LEN(@sql + c), 
   LEN(@nsql + c), 
   DATALENGTH(@sql + c), 
   DATALENGTH(@nsql + c) 
FROM @t;

How do I keep my stored procedure inputs from being silently truncated?

Use VARCHAR(8000), NVARCHAR(4000) or even N/VARCHAR(MAX), for all the variables and parameters. This way you do not need to worry about truncation when assigning @variables and @parameters. Truncation may occur at actual data write (insert or update) but that is not silent, is going to trigger a hard error and you'll find out about it. You also get the added benefit of the stored procedure code not having to be changed with schema changes (change column length, code is still valid). And you also get better plan cache behavior from using consistent parameter lengths, see How Data Access Code Affects Database Performance.

Be aware that there is a slight performance hit for using MAX types for @variables/@parameters, see Performance comparison of varchar(max) vs. varchar(N).

Is there a way to prevent SQL Server silently truncating data in local variables and stored procedure parameters?

SQL Server has no such option. You will either have to manually check the length of strings in your stored procedure and somehow handle the longer strings or use the nvarchar(max) option. If disk space isn't an issue then the nvarchar(max) option is certainly the easiest and quickest solution.

How can I make SQL Server 2012 truncate insertions if they are too big?

Normally, SQL Server will present an error on any attempt to insert more data into a field than it can hold

String or binary data would be truncated. The statement has been terminated.

SQL Server will not permit a silent truncation of data just because the column is too small to accept the data. But there are other ways that SQL Server can truncate data that is about to be inserted into a table that will not generate any form of error or warning.

By default, ANSI_WARNINGS are turned on, and certain activities such as creating indexes on computed columns or indexed views require that they be turned on. But if they are turned off, SQL Server will truncate the data as needed to make it fit into the column. The ANSI_WARNINGS setting for a session can be controlled by

SET ANSI_WARNINGS { ON|OFF }

Unlike with an insert into a table, SQL Server will quietly cut off data that is being assigned to a variable, regardless of the status of ANSI_WARNINGS. For instance:

declare @smallString varchar(5)
declare @testint int
set @smallString = 'This is a long string'
set @testint = 123.456
print @smallString
print @testint

Results is:

This 
123

This can occasionally show itself in subtle ways since passing a value into a stored procedure or function assigns it to the parameter variables and will quietly do a conversion. One method that can help guard against this situation is to give any parameter that will be directly inserted into a table a larger datatype than the target column so that SQL Server will raise the error, or perhaps to then check the length of the parameter and have custom code to handle it when it is too long.

For instance, if a stored procedure will use a parameter to insert data into a table with a column that is varchar(10), make the parameter varchar(15). Then if the data that is passed in is too long for the column, it will rollback and raise a truncation error instead of silently truncating and inserting. Of course, that runs the risk of being misleading to anyone who looks at the stored procedures header information without understanding what was done.

Source: Silent Truncation of SQL Server Data Inserts

nvarchar(max) still being truncated

To see the dynamic SQL generated, change to text mode (_shortcut: Ctrl-T), then use SELECT

PRINT LEN(@Query) -- Prints out 4273, which is correct as far as I can tell
--SET NOCOUNT ON
SELECT @Query

As for sp_executesql, try this (in text mode), it should show the three aaaaa...'s the middle one being the longest with 'SELECT ..' added. Watch the Ln... Col.. indicator in the status bar at bottom right showing 4510 at the end of the 2nd output.

declare @n nvarchar(max)
set @n = REPLICATE(convert(nvarchar(max), 'a'), 4500)
SET @N = 'SELECT ''' + @n + ''''
print @n   -- up to 4000
select @n  -- up to max
exec sp_Executesql @n

ADO.NET truncates binary data when passing it to TSQL stored procedures

Are you sure that only ADO.NET is truncating your values? I think that the stored procedure also truncates your values.

To prove it I created a simple stored procedure that has an input parameter @test nvarchar(10) and outputs the same value with a SELECT statement:

CREATE PROCEDURE testProcedure
  @test nvarchar(10)
as
BEGIN
    SELECT @test as result
END

If you call this procedure with a parameter longer than 10 the string gets truncated to 10 characters:

input

testProcedure '0123456789xxx'

output

0123456789

So your INSERT does not throw any exception because it receives the value already truncated.

Therefore if you want to get an exception from your procedure you have to give the un-truncated value to your INSERT statement.

To achieve this you must define your parameter in the stored procedure with a size bigger than the size of the field in your table, for example setting (as you said) @c nvarchar(MAX)

why this query added extra length upper(trim(LEFT(col_name::varchar, 100)))

The LEFT(string, N) function returns N characters from the left side of a string.

However, those N characters may take more than N bytes (octects), for example accented characters:

dbadmin=> select left('é', 1);
 left
------
 é

dbadmin=> select octet_length(left('é', 1));
 octet_length
--------------
            2

SQL Server data not being shown while executing with stored procedure

It looks like you'll never commit the transaction as you are not setting @Error

you declare it then use:

  IF @Error = 0
    BEGIN
      COMMIT TRANSACTION
    END

Declaring a variable and not declaring it defaults to NULL:

DECLARE @Error INT
SELECT @Error

Would return NULL

NULL <> 0 :

IF 0 = NULL
    BEGIN
        PRINT 1;
END;
    ELSE
    BEGIN
        PRINT 2;
END;

Returns 2

Check to see if there are any open transactions when you run the proc or add a WITH (NOLOCK) hint when you query the table to see if the data is there

---

Related Topics