USP_MKTASKLADDER_VALIDATESMARTFIELDDELETE

CREATE procedure dbo.[USP_MKTASKLADDER_VALIDATESMARTFIELDDELETE]
(
  @SMARTFIELDID uniqueidentifier
)
with execute as caller
as
  set nocount on;

  declare @TABLECATALOGID uniqueidentifier;

  if @SMARTFIELDID is not null 
    begin
      select
        @TABLECATALOGID = [TABLECATALOGID]
      from dbo.[SMARTFIELD]
      where [ID] = @SMARTFIELDID;

      declare @OBJECTKEY nvarchar(400);

      set @OBJECTKEY = 'VALUE.SMARTFIELD' + upper(replace(cast(@TABLECATALOGID as nvarchar(36)), '-', ''));

      if exists (select top 1 1 from dbo.[MKTASKLADDER] where [OBJECTKEY] = @OBJECTKEY)
        raiserror('The smart field cannot be deleted because it is being used by an ask ladder.', 13, 1)
    end

  return 0