8

I recently encountered an issue while porting an app to SQL Server. It turned out that this issue was caused by a stored procedure parameter being declared too short for the data being passed to it: the parameter was declared as VARCHAR(100) but in one case was being passed more than 100 characters of data. What surprised me was that SQL Server didn't 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 exhibit the same behaviour:

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

Is there an option I can enable to have SQL Server report 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)?

Luke Woodward
  • 63,336
  • 16
  • 89
  • 104
  • 1
    Duplicate of [SQL Server silently truncates varchar's in stored procedures](http://stackoverflow.com/questions/4628140/sql-server-silently-truncates-varchars-in-stored-procedures). You **can't** do it. – gbn Jan 31 '11 at 06:18
  • @gbn: Thanks for spotting the duplicate - sorry for not spotting it myself. – Luke Woodward Jan 31 '11 at 22:19

5 Answers5

4

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.

NakedBrunch
  • 48,713
  • 13
  • 73
  • 98
3

You don't have to use nvarchar(max) just use nvarchar(length+1) [e.g. if your column length is 50 then you would set the parameter to be nvarchar(51)]. See the answer from DavidHyogo - SQL Server silently truncates varchar's in stored procedures.

Community
  • 1
  • 1
dripcode
  • 31
  • 1
1

I don't know of a way to make the server do it, but I've been using the SQL Server Projects feature of Visual Studio Team System Developer Edition. It includes code analysis which caught a truncation problem of mine: using an int parameter to insert into a smallint column.

John Saunders
  • 160,644
  • 26
  • 247
  • 397
0

Though awkward, you can, however, dynamically check for parameter length before a call, e.g.

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''s too looooooooooooooooooong'

I omitted the called function's database name in the query and in the reference to INFORMATION_SCHEMA.PARAMETERS to ensure that my sample would run without edits.

I don't necessarily advocate this, but I wanted to point out that the information may be available to detect imminent truncation dynamically, if in some critical situation this is needed.

Wayne Erfling
  • 140
  • 9
-1

You can use LEFT in SQL and specified the length that you want to insert. for example.

CREATE TABLE Table1 ( test varchar(10) )

insert into Table1 values (LEFT('abcdefghijklmnopqrstuvwxyz',10))

This will insert only

abcdefghij on table

Syntax
  • 27
  • 3
  • 1
    The question is asking how to *prevent* SQL from implicitly and silently truncating strings passed to Stored Procedures. This answer tells how to explicitly truncate strings. *If* SQL server behavior was different and the question was "how do I prevent an error when I pass a too-long string" this would be an applicable answer. – Tim Sparkles Jun 13 '17 at 17:41