UFN_MKTSEGMENTATIONASKLADDER_JOINSQL

Returns a SQL statement that can be used to join to the entry amount table/view for a given Ask Ladder.

Return

Return Type
nvarchar(4000)

Parameters

Parameter	Parameter Type	Mode
@ASKLADDERID	uniqueidentifier	IN
@PARENTTABLE	nvarchar(128)	IN
@PARENTPKFIELD	nvarchar(128)	IN
@PARENTALIAS	nvarchar(128)	IN
@JOINALIAS	nvarchar(128)	IN
@TABLENAME	nvarchar(128)	IN
@PKFIELDNAME	nvarchar(128)	IN

Definition

Copy



CREATE function dbo.[UFN_MKTSEGMENTATIONASKLADDER_JOINSQL]
(
  @ASKLADDERID uniqueidentifier,
  @PARENTTABLE sysname,
  @PARENTPKFIELD sysname,
  @PARENTALIAS nvarchar(128),
  @JOINALIAS nvarchar(128),
  @TABLENAME nvarchar(128),
  @PKFIELDNAME nvarchar(128)
)
returns nvarchar(4000)
as
begin
  declare @ret nvarchar(4000);
  declare @OBJECTKEY nvarchar(400);

  select
    @OBJECTKEY = isnull([OBJECTKEY], '')
  from dbo.[MKTASKLADDER]
  where [ID] = @ASKLADDERID;

  if len(@OBJECTKEY) = 0 or @OBJECTKEY = '<none>'  -- 2 = Fixed ladder

    set @ret = null;
  else
    begin
      declare @type1 int;
      declare @type2 int;

      select @type1 = [system_type_id] from sys.columns where [object_id] = object_id(@TABLENAME) and [name] = @PKFIELDNAME;
      select @type2 = [system_type_id] from sys.columns where [object_id] = object_id(@PARENTTABLE) and [name] = @PARENTPKFIELD;

      if @type1 = @type2
        set @ret = 'left join dbo.[' + @TABLENAME + '] as [' + @JOINALIAS + '] on [' + @JOINALIAS + '].[' + @PKFIELDNAME + '] = [' + @PARENTALIAS + '].[' + @PARENTPKFIELD + ']';
      else
        set @ret = 'left join dbo.[' + @TABLENAME + '] as [' + @JOINALIAS + '] on cast([' + @JOINALIAS + '].[' + @PKFIELDNAME + '] as varchar(36)) = cast([' + @PARENTALIAS + '].[' + @PARENTPKFIELD + '] as varchar(36))';
    end

  return @ret;
end;