Auto trimming before assigning variable value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Auto trimming before assigning variable value

Author

Topic

Nerix
Starting Member

1 Post

Posted - 2013-04-30 : 15:38:19

Hi all

I am in the middle of doing some error handling with try-catch. But I have a scenario where tthe sql server dors not throw an error when assigning a value whoch is larger than the variable can handle. Example when my variable is set to nvarchar(255) and I try to assign it with value larger or longer than 255 characters long.

Below I have provided a simplified edition of my problem.

-----
Declare @value_a nvarchar(255)

Declare c_cursor cursor for
Select name from table_a

Open c_cursor
Fetch next from c_cursor into
@value_a

While @@fetch_status = 0
Begin try
Print @value_a. --This value can be larger than 255 characters
Print len(@value_a)

Fetch next from c_cursor into
@value_a
End try
Begin catch
Print 'value too long'
End catch
-----

I expected that the sqlserver will throw an error when it tries to assigns or puts a larger value into the variable @value_a. But it is not doing that. Instead it just trims/cuts the value of to 255 characters and put that part into the variable.

Are there anyway that I can force the sqlserver to throw an error instead of letting it do the auto trimming/cutting stuff?

I know I can do some manual stuff by checking on the length, but as my real example contains more than twenty columns and variables it will be clumsy and bad coding.

Hope you guys can help me out.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-04-30 : 16:51:35

No way that I know of, other than to check the length of things and taqke action accordingly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources