Double hyphen behavior in stored procedure numeric parameter

  • I ran into the following anomaly when studying the effect double hyphens have on the behavior of numeric parameters in a stored procedure. My belief (obviously incorrect) was that a parameter with a numeric data type would only accept numeric data. However my testing revealed that double hyphens in the parameter entry cause truncation, because the final digits are commented out.

    ````````````````````````````````````````

    CREATE PROCEDURE aatest

    (@AnyNumberfloat)

    AS

    BEGIN

    SELECT @AnyNumber

    END

    ```````````````````````````````````````

    DECLARE@return_value int

    EXEC@return_value = [dbo].[aatest]

    @AnyNumber = 232.3--34

    SELECT'Return Value' = @return_value

    GO

    ```````````````````````````````````````

    Result: 232.3

    ```````````````````````````````````````

    My question is...do I need to include a check for double hyphens in each stored procedure to protect against the truncation? Or am I missing something here?

    Thanks!

  • Double hyphens define a single line comment. Everything to the right of them will be ignored.

    --This is a single-line comment

    /*

    This is

    a multi-line

    comment

    */

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Thanks. I understand the comment portion. What has me confused/concerned is that a stored procedure with a parameter of data type float will take a non-numeric value like 232.3--34 and simply accept the -- as comment indicator. Shouldn't the data type prevent a non-numeric character from processing? All other special characters, including a single hyphen fail the numeric test.

  • --Comment removed after re-reading the post more closely 🙂

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • lynns (7/25/2016)


    Thanks. I understand the comment portion. What has me confused/concerned is that a stored procedure with a parameter of data type float will take a non-numeric value like 232.3--34 and simply accept the -- as comment indicator. Shouldn't the data type prevent a non-numeric character from processing? All other special characters, including a single hyphen fail the numeric test.

    The stored procedure is only taking the 232.34 as a value and that's a numeric value. The --34 is ignored as it's a comment, it's never passed to the stored procedure.

    If you had something like

    EXEC@return_value = [dbo].[aatest] @AnyNumber = '232.3--34'

    The value would be a non-numeric value and the procedure will fail.

    CREATE PROCEDURE aatest

    (@AnyNumberfloat)

    AS

    SELECT @AnyNumber

    GO

    --This runs fine

    DECLARE@return_value int

    EXEC@return_value = [dbo].[aatest] @AnyNumber = 232.3--34

    SELECT'Return Value' = @return_value

    GO

    --This fails

    DECLARE@return_value int

    EXEC@return_value = [dbo].[aatest] @AnyNumber = '232.3--34'

    SELECT'Return Value' = @return_value

    GO

    DROP PROC aatest

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Thanks! I think I see the light now. I appreciate your help.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply