SQL: Adding getdate() as an extended property with EXEC sys.sp_updateextendedproperty

Question

Situation: Trying to build out basic table notes utilizing extended properties on tables/views/sp and identifying a date where the extended property was modified to kick off events where a review is needed.

I have attempted a few approaches but cannot identify a syntax to assign a getdate value to the description below. I have also tried identifying a system table that would record a modified date, this would allow the user to not have to enter a modified date manually.

EXEC sys.sp_updateextendedproperty 
@name=N'ModifiedDate', @value= getdate(), 
@level0type=N'SCHEMA',
@level0name=N'dbo',
@level1type=N'TABLE',
@level1name=N'tablename'

Incorrect syntax near ')'.

Any help or direction on syntax is much appreciated. The documentation is still in infancy, if there is a better solution I am open to those opinions.

It is a duplicate of https://stackoverflow.com/questions/5967035/using-function-as-a-parameter-when-executing-a-stored-procedure — Venkatesh R, Aug 31 '18 at 01:00
Thanks for the helpful response Venkatesh -- I missed this one in my search earlier. — A.Rennick, Aug 31 '18 at 01:47

score 0 · Accepted Answer · answered Aug 30 '18 at 14:53

You need to use variable or literal:

DECLARE @d SQL_VARIANT = getdate();

EXEC sys.sp_updateextendedproperty 
@name=N'ModifiedDate', @value= @d,
@level0type=N'SCHEMA',
@level0name=N'dbo',
@level1type=N'TABLE',
@level1name=N'tablename';

EXECUTE:

[ @parameter = ] { value
| @variable [ OUTPUT ]
| [ DEFAULT ]
}

SQL: Adding getdate() as an extended property with EXEC sys.sp_updateextendedproperty

1 Answers1