August 27, 2014 at 12:55 pm
First post here so be kind.
I ran across and interesting issue today. Thought I would share it with the community.
This query was run against a database on 2008 SP3 and 2012 SP2
Here is an example of what I ran
Declare @A_number varchar(5)
Select Top 1 Column_Is_an_int = @A_number
From Our_Main_Table
Where A_Database_Name = 'A database with many records in this table and multiple records in the Column_is_an_int some are more than 5 characters'
Select @A_number
Exec My_Stored_Proc @A_Nmuber
The top result for that database had an integer that was 8 characters including the - sign.
When the query ran I would normally expect it to throw the binary data cannot be truncated error. However in this case it returned a * in the variable which then tried to pass it in to the SP which of course threw a fit.
Once I changed the varchar(5) to varchar(50) it worked perfectly.
I am not sure if this is a known error but I thought I would share my solution just in case any other new DBA's had this happen to them.
Cheers
Dennis
August 27, 2014 at 1:56 pm
This is not an error. The * indicates that the value is too large for the currently defined size of the variable.
August 27, 2014 at 2:13 pm
Every time in the past I have encountered something like this, i.e. trying to put 10 charcters in a 9 character field. It has throw the binary data cannot be truncated error. Is that not the case for variables then?
PS I am still super new as a dba
August 27, 2014 at 2:31 pm
Are you sure you have the right syntax?
Select Top 1 Column_Is_an_int = @A_number from ....
Unless I didn't get enough sleep and/or not enough coffee, this is not assigning a value to the variable, at least, not as I see it.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 27, 2014 at 2:39 pm
Sorry typed it wrong! It is
Select Top 1 @A_number=Column_Is_an_int
From Our_Main_Table
August 27, 2014 at 3:20 pm
Your assignment is casting your integer to a varchar. If the result doesn't fit the variable then it will be truncated or rounded.
If you have a look at the Truncating and Rounding Results it states that for int, smallint and tinyint going to a varchar truncation will result in a *
The following demonstrates the behaviour of a few conversions. Only the last float conversion will error.
DECLARE @var varchar(5)
SELECT @var = 'bob'
print @var
SELECT @var = 'bob and bill'
print @var
SELECT @var = 12345
print @var
SELECT @var = 123456789
print @var
SELECT @var = 12.34
print @var
SELECT @var = 123.45
print @var
If you want to get an error you could try casting you integer to a numeric/decimal before assigning it eg
Select Top 1 @A_number = CAST(Column_Is_an_int AS Numeric(10,0))
From Our_Main_Table
Where A_Database_Name = 'A database with many records in this table and multiple records in the Column_is_an_int some are more than 5 characters'
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply