Stored Procedure string parameter accepts default value without quotes

Supposing that we define a parameter with string data type such as NVARCHAR. We can assign a default value for this parameter usually using quotes. So, the following code must encounter an error:

CREATE PROC uspStringWithoutQuotes
  @Param NVARCHAR(20) = Hello
PRINT @Param + '!';

Although, we assign the string “Hello” without using quotes, this code will create the stored procedure. If we call this stored procedure using the default value for the parameter, it passes the string “Hello” to the parameter @Param, like follow picture:

EXEC uspStringWithoutQuotes @Param = DEFAULT;

Although, I reported this problem as a bug in this link , the SQL Server Development Team expressed that while they agree that this behavior is not optimal, it has been in the product for a long time and therefore, they cannot easily change it, as it would break existing applications. Thanks to their fast response, we must avoid using this pattern in our codes until it will be fixed.

This entry was posted in T-SQL Tips & Tricks and tagged , , . Bookmark the permalink.