September 25, 2017 at 9:23 am
I created the following proc in Sql Server 10.50.6220
CREATE PROC test
@param int = NULLL -- typing error, 3 L
AS
SELECT 0
GO
-- compilation ok
exec test @param = 1 -- OK
GO
EXEC test -- KO
GO
DROP PROC test
GO
Why sql server does not detect that NULLL is not a valid value?
September 25, 2017 at 9:51 am
My question was, as NULLLLLLL is not a valid value, why my sp compiles with no error?
September 25, 2017 at 9:58 am
phamminh - Monday, September 25, 2017 9:51 AMMy question was, as NULLLLLLL is not a valid value, why my sp compiles with no error?
Because the default value is determined at run time, not at creation. Thus the error generates when you execute the sp without a value because SQL server attempts to assign the value NULLL to an int datatype.:
Msg 245, Level 16, State 1, Procedure test, Line 0 [Batch Start Line 14]
Conversion failed when converting the nvarchar value 'NULLL' to data type int.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 25, 2017 at 9:58 am
phamminh - Monday, September 25, 2017 9:51 AMMy question was, as NULLLLLLL is not a valid value, why my sp compiles with no error?
It means it's an optional parameter and a value doesn't need to be passed. That's how you specify optional parameters.
Sue
September 25, 2017 at 10:04 am
Thank you.
I believe that error can be detected at compile time. Is there any "good" reason they did not implement it?
September 25, 2017 at 10:15 am
phamminh - Monday, September 25, 2017 10:04 AMThank you.I believe that error can be detected at compile time. Is there any "good" reason they did not implement it?
It possibly could be, but that's not how SQL Server works.
If you feel this is a issue, you'd be best making a Connect item or up voting an existing one (if someone has already created one for this feature).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply