SQL SERVER – Puzzle – SQL Server Stored Procedure and Unexpected Results

Today’s puzzle is going to be an interesting puzzle for many. The matter of the fact when I discovered this puzzle related to the stored procedure and unexpected results, I was very much surprised as well.

Let us see the puzzle right away.

First, let us following code where I have declared a varchar and right after that I am storing some value in it and selecting it afterward.

Solarwinds
DECLARE @phone_no varchar(8) 
SET @phone_no=910034568
SELECT @phone_no as phone_no

When you run the above code, it gives us following strange results.

Here is the Puzzle:

When we run above query, why is it resulting result as *, instead of the value stored in a variable?

SQL SERVER - Puzzle - SQL Server Stored Procedure and Unexpected Results unexpectedresult

Please leave your answer in the comments section and I will publish the answers next week at the same time. I believe this puzzle is simple enough but not as easy as you think. I suggest you may ask this question to your work colleagues and see if they know the answer to this puzzle.

Here are few additional puzzles, if you want to try to tease your brain:

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, ,
Previous Post
SQL SERVER – Puzzle – How Does Table Qualifier Work in INSERT Statement?
Next Post
SQL SERVER – Puzzle – How Does YEAR Function Work?

Related Posts

50 Comments. Leave new

  • So VARCHAR is holding only the characters you assigned to it and that’s why retrieve as *, which means there are different amount of characters than 8 :)

    You have specified a string as Varcha(8) and your length is 9 digits, then you got the * as result, because you are not specifing the digits into a quotes E.i.(SET @phone_no=’910034568′). Then your result would be for example: [91003456]

    However if you specify the correct length as Varchar(9) and your Set is like SET @phone_no=910034568 then you get the correct result.

    Reply
  • So VARCHAR is holding only the amount of characters you assigned to it and that’s why retrieve as *, which means there are different amount of characters than 8 :)

    You have specified a string as Varcha(8) and your length is 9 digits, then you got the * as result, because you are not specifying the digits into a quotes E.i.(SET @phone_no=’910034568′). then your result would be for example: [91003456]

    However if you specify the correct length as Varchar(9) and your Set is like SET @phone_no=910034568 then you get the correct result.

    Reply
  • Sanket Patole
    March 12, 2018 3:43 pm

    The size of varchar declared is 8 while the phone no is 10 digits

    Reply
  • There is a implicit conversion of integer to varchar and it returns back an * implying that an error has occurred.

    Reply
  • You have defined less length than actual variable value length. that’s why this type of result you got.

    Reply
  • Ramkumar Sambandam
    July 2, 2018 11:38 pm

    implicit conversion failed due to 9 character in input as variable declared as varchar(8) which caused the result to *

    Reply
  • Pinal could you explain this. Have we got the right answer?

    Reply
  • This is implicit conversion issue when you are trying to convert 9 digits of number to varchaar which resulted in output as * .But when we write same query with number in single quote ‘910034568’ we can see output as 91003456.

    Reply
  • Chris Mottram
    March 29, 2019 4:40 pm

    * means everything. This is an overflow situation where all of the available destination varchar field is used, and then some. If you assign it with single quotes, the string assignation left justifies and truncates, but that never gets called on assignation of the implicit conversion from int to varchar(8).

    It makes perfect sense. Justification and truncation of a string still gives a fair representation of the value, but doing it for a number means that it could be many factors of 10 incorrect. It’s far better to throw an error: For example:

    DECLARE @phone_no varchar(8)
    SET @phone_no=910034568
    SELECT CONVERT(int, @phone_no) as phone_no

    Gives:

    Msg 245, Level 16, State 1, Line 3
    Conversion failed when converting the varchar value ‘*’ to data type int.

    Imagine if that number went on to used in an interest calculation on your savings. If it was a factor of 10 out, it’s better it causes a problem at source, rather than a factor of 10 problem in your savings! ;-)

    Reply
  • Refer:https://docs.microsoft.com/en-us/sql/t-sql/data-types/int-bigint-smallint-and-tinyint-transact-sql?view=sql-server-2017
    Converting integer data
    When integers are implicitly converted to a character data type, if the integer is too large to fit into the character field, SQL Server enters ASCII character 42, the asterisk (*).

    Integer constants greater than 2,147,483,647 are converted to the decimal data type, not the bigint data type. The following example shows that when the threshold value is exceeded, the data type of the result changes from an int to a decimal.

    Reply

Leave a Reply

Menu