UFN_SELECTIONMERGE_BUILDIDSELECTSQL

Return

Return Type
nvarchar(max)
Parameters

Parameter	Parameter Type	Mode
@SELECTIONID1	uniqueidentifier	IN
@SELECTIONID2	uniqueidentifier	IN
@MERGETYPECODE	tinyint	IN
Definition

Copy


create function dbo.[UFN_SELECTIONMERGE_BUILDIDSELECTSQL]
(
  @SELECTIONID1 uniqueidentifier,
  @SELECTIONID2 uniqueidentifier,
  @MERGETYPECODE tinyint = 0
)
returns nvarchar(max)
as
begin
  declare @SELECTIONSQL1 nvarchar(max);
  declare @SELECTIONSQL2 nvarchar(max);
  declare @SQL nvarchar(max);

  if @MERGETYPECODE is null
    set @MERGETYPECODE = 0;

  --Get the "select" statement sql for each selection...

  select
    @SELECTIONSQL1 = dbo.[UFN_SELECTIONHELPER_BUILDIDSELECTSQL](@SELECTIONID1),
    @SELECTIONSQL2 = dbo.[UFN_SELECTIONHELPER_BUILDIDSELECTSQL](@SELECTIONID2);

  --Build the selection merge sql...

  if @SELECTIONSQL1 is not null and @SELECTIONSQL2 is not null
    begin
      if @MERGETYPECODE = 0  --Union

        set @SQL = '  ' + @SELECTIONSQL1 + char(13) +
                   '  union' + char(13) +
                   '  ' + @SELECTIONSQL2 + char(13);

      else if @MERGETYPECODE = 1  --Intersection

        set @SQL = '  ' + @SELECTIONSQL1 + char(13) +
                   '  intersect' + char(13) +
                   '  ' + @SELECTIONSQL2 + char(13);

      else if @MERGETYPECODE = 2  --Only in first (left minus right)

        set @SQL = '  ' + @SELECTIONSQL1 + char(13) +
                   '  except' + char(13) +
                   '  ' + @SELECTIONSQL2 + char(13);

      else if @MERGETYPECODE = 3  --Only in second (right minus left)

        set @SQL = '  ' + @SELECTIONSQL2 + char(13) +
                   '  except' + char(13) +
                   '  ' + @SELECTIONSQL1 + char(13);

      else if @MERGETYPECODE = 4  --Exclusive Or

        set @SQL = '  (' + char(13) +
                   '    ' + @SELECTIONSQL1 + char(13) +
                   '    union' + char(13) +
                   '    ' + @SELECTIONSQL2 + char(13) +
                   '  )' + char(13) +
                   '  except' + char(13) +
                   '  (' + char(13) +
                   '    ' + @SELECTIONSQL1 + char(13) +
                   '    intersect' + char(13) +
                   '    ' + @SELECTIONSQL2 + char(13) +
                   '  )' + char(13);
    end

  return @SQL;
end