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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Auto trimming before assigning variable value
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

1 Posts

Posted - 04/30/2013 :  15:38:19  Show Profile  Reply with Quote
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

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
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.

Flowing Fount of Yak Knowledge

4614 Posts

Posted - 04/30/2013 :  16:51:35  Show Profile  Reply with Quote
No way that I know of, other than to check the length of things and taqke action accordingly.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000