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.

Unexpected Results

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

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

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

Here is the Puzzle: Unexpected Results

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:

Update: The correct answer to this puzzle is given by Shweta in the comments section.

Please feel free to reach out to me on twitter.

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

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

  • Was it because you tired to set the variable to more characters than the amount that was set (8)?

    Reply
  • Here length of the @phone_no is varchar(8) and we try to set the value which is of 9 characters (@phone_no=910034568). So if any integer value which is much large to fit into the varchar then sql server return asterisk (*) character (which is internally an overflow exception).

    Reply
  • Bruno Bragatto
    March 7, 2018 1:39 am

    This output occurs because the NUMBER set for the @phone_no variable exceeds the maximum length defined for that variable.
    But, if the same data (in that case a number) is single-quoted, the output data is truncated because the SQL Server will deal with this data as a character string.

    For example: SET @phone_no=’910034568′ in that case the output for the same select is “91003456” because the SQL Server truncates the character string until the limit of the lenghl defined.

    The solution for that puzzle is to increase the length of the varchar to 9.

    Reply
  • Mikhail Zemelshteyn
    March 7, 2018 2:24 am

    varchar length is 8 and the phone number is 9 digits long ?

    Reply
  • It’s documented behavior: https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017#truncating-and-rounding-results ;) trying to store an integer with 9 digits in a 8 char variable would give some kind of error/overflow issue, in this case it is always handled as ‘*’

    Reply
  • The reason behind is that it prints character value of 910034568

    select cast (910034568 as varchar(8)) also prints *.

    Reply
  • It would function just ok had the constant that is fed in the variabel be no longer than 8 characters. It would be converted to a string representing the number. But the number is 9 figures long and doesn’t fit.
    An error about the size would be more appropriate or about wrong data type…

    Kees

    Reply
  • Because the variable is set from an integer that is too long to fit in the length available, the implicit integer to varchar conversion results in ‘*’, meaning the result length is too short to display it.

    Reply
  • Ravi Kant Sharma
    March 7, 2018 5:04 pm

    because size is varchar(8) and phone no has 9 digit.

    Reply
  • danielsonsvk
    March 7, 2018 6:04 pm

    The number provided is long 9 characters

    Reply
  • Darshan Shah
    March 7, 2018 6:53 pm

    The unexpected result is because declare variable @phone_no length is varchar(8) and the value set to that variable has 9 character.

    Reply
  • First, only VARCHAR(8), not VARCHAR(9). Second, no single quotes to enter phone_no.

    Reply
  • J the Cyclops
    March 7, 2018 11:27 pm

    The code tries to convert 9 digit integer to varchar(8), which is arithmetic overflow error. However, instead of throwing the error, it shows ‘*’ instead because arithmetic overflow error was not applied to data type from original sql spec. You will get error message if you try with nvarchar(8).

    Reply
  • Were can i find the valid reason

    Reply
  • how did that ouput generated

    Reply
  • The value is of 9 characters while varchar size is 8. Varchar is very old datatypes than new once such as NVARCHAR of SQL. At that time Microsoft developers might have suppress such exceptions and replace it with *.
    Further , when we try to same scenario with below query:-
    DECLARE @phone_no Nvarchar(8)
    SET @phone_no=910034568
    SELECT @phone_no as phone_no
    we get exception message as “Msg 8115, Level 16, State 2, Line 2
    Arithmetic overflow error converting expression to data type nvarchar.”

    Which means later in the days developers at Microsoft has decided to raise proper exception message rather than replacing it with *.

    Reply
  • Because history. Back in the original specifications for Structured Query Language, they defined the behavior for this interaction, where an int value was placed into a varchar field too short to display the full number as returning an asterisk.

    Later, as SQL developed, they changed the behavior to return an overflow error. But so as to not break existing systems, the early data types, such as int and varchar retained their existing behavior. If you change the varchar variable to an Nvarchar variable, this will generate an error instead.

    Reply
  • Is it because @phone_no is 8 characters but ‘910034568’ is 9 characters when implicitly converted to a string?

    Increasing @phone_no to 9 characters (or deleting a character from 910034568) returns a proper value.

    Reply
  • length of number your are trying to store is greater than the declared varchar length

    Reply
  • SURENDRA REDDY
    March 9, 2018 12:50 pm

    because value (@phone_no=910034568) length is more then datatype length.

    Reply

Leave a Reply

Menu