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)?

Improve this question
2

5 Answers 5

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I had suspected that this was the case but I thought I'd ask anyway. Thanks for confirming my suspicion.
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.

Improve this answer

Comments

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.

Improve this answer

Comments

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.

Improve this answer

Comments

-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

Improve this answer

1 Comment

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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.