USP_SMARTFIELD_UPDATEQUERYVIEWRELATIONSHIPS

CREATE procedure dbo.USP_SMARTFIELD_UPDATEQUERYVIEWRELATIONSHIPS
(
  @ID nvarchar,
  @CHANGEAGENTID uniqueidentifier
)
as
  set nocount on;

  declare @SMARTFIELDID uniqueidentifier;

  -- declare a cursor that returns all smart field IDs

  declare smartfield_cursor cursor local FAST_FORWARD for
    select ID from dbo.SMARTFIELD

  OPEN smartfield_cursor

  FETCH NEXT from smartfield_cursor 
  into @SMARTFIELDID;

  -- Check @@FETCH_STATUS to see if there are any more rows to fetch.

  WHILE @@FETCH_STATUS = 0
  begin
    begin try
      -- update query view relationships for the smart field

      exec dbo.USP_SMARTFIELD_UPDATESMARTFIELDQUERIES @SMARTFIELDID, 0, @CHANGEAGENTID;
    end try

    begin catch
      CLOSE smartfield_cursor;
      DEALLOCATE smartfield_cursor;
      exec dbo.USP_RAISE_ERROR;
      return 1;
    end catch

    FETCH NEXT from smartfield_cursor into @SMARTFIELDID;
  end

  CLOSE smartfield_cursor;
  DEALLOCATE smartfield_cursor;

  return 0;