USP_MKTRECORDSOURCE_FINDINRECORDSOURCE

CREATE procedure dbo.[USP_MKTRECORDSOURCE_FINDINRECORDSOURCE]
(
  @RECORDID nvarchar(36),
  @RECORDSOURCEID uniqueidentifier output,
  @RECORDSOURCENAME nvarchar(255) output,
  @PRIMARYKEYFIELD nvarchar(255) output
)
as
  set nocount on;

  declare @SQL nvarchar(max);
  declare @ID nvarchar(36);

  --Search all record sources...

  declare RECORDSOURCECURSOR cursor local fast_forward for
    select
      [MKTRECORDSOURCE].[ID],
      [QUERYVIEWCATALOG].[OBJECTNAME],
      [QUERYVIEWCATALOG].[PRIMARYKEYFIELD]
    from dbo.[MKTRECORDSOURCE]
    inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTRECORDSOURCE].[ID]
    where dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([MKTRECORDSOURCE].[ID]) = 1;

  open RECORDSOURCECURSOR;
  fetch next from RECORDSOURCECURSOR into @RECORDSOURCEID, @RECORDSOURCENAME, @PRIMARYKEYFIELD;

  while (@@FETCH_STATUS = 0)
  begin
    set @ID = null;

    set @SQL =    'select @ID = cast([' + @PRIMARYKEYFIELD + '] as nvarchar(36))' + char(13) +
                'from dbo.[' + @RECORDSOURCENAME + '] ' + char(13) +
                'where [' + @PRIMARYKEYFIELD + '] = @RECORDID';
    exec sp_executesql @SQL, N'@ID nvarchar(36) output, @RECORDID nvarchar(36)', @ID = @ID output, @RECORDID = @RECORDID;

    if @ID is not null
      break;

    fetch next from RECORDSOURCECURSOR into @RECORDSOURCEID, @RECORDSOURCENAME, @PRIMARYKEYFIELD;
  end;

  close RECORDSOURCECURSOR;
  deallocate RECORDSOURCECURSOR;

  if @ID is null
    begin
      set @RECORDSOURCEID = null;
      set @RECORDSOURCENAME = null;
      set @PRIMARYKEYFIELD = null;
    end;

  return 0;