July 25, 2016 at 12:52 pm
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!
July 25, 2016 at 12:59 pm
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
*/
July 25, 2016 at 1:02 pm
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.
July 25, 2016 at 1:08 pm
--Comment removed after re-reading the post more closely
July 25, 2016 at 1:11 pm
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
July 25, 2016 at 1:32 pm
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy