USP_JOINNEEDSCAST

Used to determine casting on join of two tables.

Parameters

Parameter	Parameter Type	Mode
@TABLEVIEW1	nvarchar(255)	IN
@TABLEVIEWID1	nvarchar(255)	IN
@TABLEVIEW2	nvarchar(255)	IN
@TABLEVIEWID2	nvarchar(255)	IN
@NEEDSCAST	bit	INOUT

Definition

Copy



CREATE procedure [dbo].[USP_JOINNEEDSCAST]
(
  @TABLEVIEW1 nvarchar(255),
  @TABLEVIEWID1 nvarchar(255),
  @TABLEVIEW2 nvarchar(255),
  @TABLEVIEWID2 nvarchar(255),
  @NEEDSCAST bit output
)
as
  set nocount on;

  declare @ret bit;
  declare @SQL nvarchar(max);

  set @NEEDSCAST = 0;

  begin try
    set @SQL ='select @NEEDSCAST=(case when t1.[name] = t2.[name] then 0 else 1 end) ' +
          'from sys.columns AS c1 inner join sys.types AS t1 ON c1.user_type_id=t1.user_type_id, ' +
          '        sys.columns AS c2 inner join sys.types AS t2 ON c2.user_type_id=t2.user_type_id ' +
          '    where ' +
          '    (c1.object_id = OBJECT_ID(''' + @TABLEVIEW1 + ''') and c1.[NAME]=''' + @TABLEVIEWID1 + ''')' +
          '    and ' +
          '    (c2.object_id = OBJECT_ID(''' + @TABLEVIEW2 + ''') and c2.[NAME]=''' + @TABLEVIEWID2 + ''')'

    exec sp_executesql @SQL, N'@NEEDSCAST bit output', @NEEDSCAST = @NEEDSCAST output;
  end try

  begin catch
    exec dbo.USP_RAISE_ERROR;
    return 1;
  end catch

  return 0;