SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

Nerix
Starting Member

Denmark
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
@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
Flowing Fount of Yak Knowledge

4612 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  
 New 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.17 seconds. Powered By: Snitz Forums 2000