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 🙂
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
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