Why can't I use an inline expression in sp_addextendedproperty but I can use a variable?

Question

When I run this T-SQL on a SQL Server 2016 database with compatibility level 130 I get an error:

DECLARE @myVariable int = 4;

EXEC sys.sp_addextendedproperty
    @name = N'MyExtendedProperty',
    @value = FORMAT( @myVariable, 'd', 'en-US' ), 
    @level0type = N'SCHEMA', @level0name=N'dbo',
    @level1type = N'TABLE' , @level1name=N'MyTable';

The error message is below, and SSMS highlights the use of @myVariable within the FORMAT function call:

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '@myVariable'

But if I use an intermediate variable the SQL runs successfully:

DECLARE @myVariable int = 4;

DECLARE @myVariableText nvarchar(10) = FORMAT( @myVariable, 'd', 'en-US' )

EXEC sys.sp_addextendedproperty
    @name = N'MyExtendedProperty',
    @value = @myVariableText, 
    @level0type = N'SCHEMA', @level0name=N'dbo',
    @level1type = N'TABLE' , @level1name=N'MyTable';

I did wonder if FORMAT or sp_addextendedproperty is a magical function like RAISERROR which requires the first argument to be a string literal (no expressions allowed) but the documentation for sp_addextendedproperty does not mention any restrictions on the @value parameter:

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addextendedproperty-transact-sql?view=sql-server-2017

[ @value= ] { 'value'}

Is the value to be associated with the property. value is sql_variant, with a default of NULL. The size of value cannot be more than 7,500 bytes.

Answer 1

When you pass values to a stored procedure, you can only pass values, and stored procedures do not allow expressions to be evaluated.

exec myProc 2    is ok

exec myProc @SomeIntValue    is ok

But,

exec myProc 2 + 2   is NOT ok.

So while a simple set, such as

DECLARE @i as int

Set @i = (2 + 2)

exec myProc @i

The above is ok, since you can ONLY pass a “value” to a stored procedure. You cannot pass an expression. In effect there is no evaluation or expression service available for the parameters you pass to a stored procedure.

So this issue is NOT limited to the use of sys.sp_addextendedproperty, nor is this a SPECIAL case of some kind. The issue you face applies to any kind of call and use of a stored procedure.

So in a set, or say select, you can use expressions, but not for passing values to a stored procedure.