Error when calling sp_addextendedproperty from another stored procedure

Question

I try to add extended properties to my table and column, but since SQL Server separates the add and update stored procedure, and need too many unused parameters for my case, I decide to create wrapper for these 2 stored procedures so I can create description with only 3 parameters needed for add or update on table and column level. This my current code:

CREATE PROCEDURE sp_addorupdatedesc 
    @tableName varchar,
    @columnName varchar = NULL, 
    @objectDescription varchar
AS
BEGIN
    IF (@columnName IS NULL)
    BEGIN
        IF NOT EXISTS (SELECT 1 FROM fn_listextendedproperty (NULL, 'user', 'dbo', 'table', default, NULL, NULL) WHERE OBJNAME=@tableName)
        BEGIN
            EXECUTE sp_addextendedproperty 'MY_DESCRIPTION', @objectDescription, 'user', dbo, 'table', @tableName, default, NULL
        END
        ELSE
        BEGIN
            EXECUTE sp_updateextendedproperty 'MY_DESCRIPTION', @objectDescription, 'user', dbo, 'table', @tableName, default, NULL
        END
    END
    ELSE
    BEGIN
        IF NOT EXISTS (SELECT 1 
                       FROM sys.extended_properties AS ep
                       INNER JOIN sys.tables AS t ON ep.major_id = t.object_id 
                       INNER JOIN sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id = c.column_id
                       WHERE class = 1 AND T.NAME=@tableName AND C.name = @columnName)
        BEGIN
            EXECUTE sp_addextendedproperty 'MY_DESCRIPTION', @objectDescription, 'user', dbo, 'table', @tableName, 'column', @columnName
        END
        ELSE
        BEGIN
            EXECUTE sp_updateextendedproperty 'MY_DESCRIPTION', @objectDescription, 'user', dbo, 'table', @tableName, 'column', @columnName
        END
    END 
END
GO

But when I use this stored procedure, I get this error instead:

Msg 15135, Level 16, State 8, Procedure sp_addextendedproperty, Line 58
Object is invalid. Extended properties are not permitted on 'dbo.P.P', or the object does not exist.

Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, Jan 31 '17 at 10:28
[Bad habits to kick : declaring VARCHAR without (length)](https://sqlblog.org/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length) - you should **always** provide a length for any `varchar` variables and parameters that you use. When you define a **parameter** as `varchar` - without any length - it defaults to **EXACTLY 1 CHARACTER** length - typically **NOT** what you want! So **always** use a length!! So your table and column names as well as the description all get **truncated** to a **single character!** — marc_s, Jan 31 '17 at 10:30
Ah... so this error was because the size.... after i change the size of the varchar this sp works! thanks again for your another sugestion :D — Angga, Jan 31 '17 at 10:37

score 1 · Accepted Answer · edited Jan 31 '17 at 11:04

I changed my stored procedure as marc_S suggested and it works now.

This may be a clumsy mistake, but I hope if anyone need to simplify add or update extended property to table or column in one stored procedure with only 3 parameters "TableName", "ColumnName", "Description".

Here is the code.

CREATE PROCEDURE setdescription 
    @tableName varchar(100),
    @columnName varchar(100) = NULL, 
    @objectDescription varchar(250)
AS
BEGIN
    IF (@columnName IS NULL)
        BEGIN
            IF NOT EXISTS (SELECT 1 FROM fn_listextendedproperty (NULL, 'user', 'dbo', 'table', default, NULL, NULL) WHERE OBJNAME=@tableName)
                BEGIN
                    EXECUTE   sp_addextendedproperty 'MY_DESCRIPTION', @objectDescription, 'user', dbo, 'table', @tableName, default, NULL
                END
            ELSE
                BEGIN
                    EXECUTE   sp_updateextendedproperty 'MY_DESCRIPTION', @objectDescription, 'user', dbo, 'table', @tableName, default, NULL
                END
        END
    ELSE
        BEGIN
            IF NOT EXISTS (SELECT 1 FROM sys.extended_properties AS ep
                                INNER JOIN sys.tables AS t ON ep.major_id = t.object_id 
                                INNER JOIN sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id = c.column_id
                                WHERE class = 1 AND T.NAME=@tableName AND C.name = @columnName)
                BEGIN
                    EXECUTE  sp_addextendedproperty 'MY_DESCRIPTION', @objectDescription, 'user', dbo, 'table', @tableName, 'column', @columnName
                    --EXECUTE   sp_addextendedproperty @name=N'CXC_DESCRIPTION', @value=N@temp3, @level0type=N'user', @level0name=N'dbo', @level1type=N'table', @level1name=N@temp1, @level2type=N'column', @level2name=N@temp2
                END
            ELSE
                BEGIN
                    EXECUTE   sp_updateextendedproperty 'MY_DESCRIPTION', @objectDescription, 'user', dbo, 'table', @tableName, 'column', @columnName
                END
        END 
END
GO

Error when calling sp_addextendedproperty from another stored procedure

1 Answers1