In enter link description here I showed, how I document databases.
To insert the extended properties, initially I uses plan sequences of sp_addextendedproperty calls.
But lately I wanted to catch the errors about not existing objects when I run the script on an older version of the database.
I found the following working code by trial and error
begin try
drop procedure dbo.BK_add_Tableproperty
end try begin catch end catch
go
create procedure dbo.BK_add_Tableproperty (
@table_name sysname,
@property_name nvarchar(max),
@Property_value sysname
)
as
declare @error integer;
begin try
EXEC sys.sp_addextendedproperty @name= @property_name,
@value = @Property_value,
@level0type=N'SCHEMA',
@level0name=N'dbo',
@level1type=N'TABLE',
@level1name=@table_name
end try begin catch
SET @error = @@ERROR;
if @error = 15135
Print 'Table missing no extended properties added: ' + @table_name
else if @error = 15233
Print 'Table has already property: ' + @table_name + ' - ' + @property_name
else
Print 'unexpected error ' + str(@error)
ROLLBACK;
end catch
go
What me confuses, is the fact, that I have to add a ROLLBACK to the catch path. At msdn I found no hint, that sp_addextendedproperty uses transactions. Can anyone explain this?
Answer to gbn:
But return exits unconditionally the procedure. I see only 1 commit in each path:
BEGIN TRANSACTION
begin
EXEC %%ExtendedPropertySet().AddValue(Name = @name, Value = @value, Level0type = @level0type, Level0name = @level0name, Level1type = @level1type, Level1name = @level1name, Level2type = @level2type, Level2name = @level2name)
IF @@error <> 0
begin
COMMIT TRANSACTION
return (1)
end
end
COMMIT TRANSACTION
return (0)
Reported as bug to Microsoft connect:
Improved version (better error handling and fixed parameter type. (the original problem is unaffected, but this handels NLS in erropr messages)
create procedure dbo.BK_add_Columnproperty (
@table_name sysname,
@Column_name sysname,
@property_name nvarchar(max),
@Property_value sql_variant
)
as
begin try
--SET XACT_ABORT ON
EXEC sys.sp_addextendedproperty @name= @property_name,
@value = @Property_value,
@level0type=N'SCHEMA',
@level0name=N'dbo',
@level1type=N'TABLE',
@level1name=@table_name,
@level2type=N'COLUMN',
@level2name=@Column_name
end try begin catch
-- SELECT XACT_STATE() -- returns 1 or -1 when SET XACT_ABORT ON
if ERROR_NUMBER() = 15135 -- object invalid
print ' ' + str(ERROR_NUMBER()) + ' -- ' + ERROR_MESSAGE()
else
print '##### ' + str(ERROR_NUMBER()) + ' -- ' + ERROR_MESSAGE()
ROLLBACK;
end catch
go