USP_BBDW_SELECTION_GETVALUES

CREATE procedure [dbo].[USP_BBDW_SELECTION_GETVALUES](
  @DBTABLENAME nvarchar(128), 
  @PRIMARYKEYTYPENAME nvarchar(128)
) 
as 
  --This NOP is required for the SSIS development environment to be able to detect the result set column metadata.

  if 1=null
    select 
      newid() [ID_UNIQUEIDENTIFIER],
      cast('' as nvarchar(max)) [ID_NVARCHAR],
      cast( 0 as int) [ID_INT], 
      cast( 0 as bigint) [ID_BIGINT]

  declare @SQL nvarchar(max) = '';

  if @PRIMARYKEYTYPENAME = 'uniqueidentifier'
  begin  
    set @sql =  'select ' + char(13) +
                '  [ID] [ID_UNIQUEIDENTIFIER],' + char(13) +
                '  cast(null as nvarchar(max)) [ID_NVARCHAR],' + char(13) +
                '  null [ID_INT],' + char(13) +
                '  null [ID_BIGINT]' + char(13) + 
                'from dbo.[' + @DBTABLENAME + '];';
  end
  else if @PRIMARYKEYTYPENAME like 'nvarchar%'
  begin  
    set @sql =  'select ' + char(13) +
                '  null [ID_UNIQUEIDENTIFIER],' + char(13) +
                '  cast([ID] as nvarchar(max)) [ID_NVARCHAR],' + char(13) +
                '  null [ID_INT],' + char(13) +
                '  null [ID_BIGINT]' + char(13) + 
                'from dbo.[' + @DBTABLENAME + '];';
  end
  else if @PRIMARYKEYTYPENAME = 'int' or @PRIMARYKEYTYPENAME = 'integer'
  begin  
    set @sql =  'select ' + char(13) +
                '  null [ID_UNIQUEIDENTIFIER],' + char(13) +
                '  cast(null as nvarchar(max)) [ID_NVARCHAR],' + char(13) +
                '  [ID] [ID_INT],' + char(13) +
                '  null [ID_BIGINT]' + char(13) + 
                'from dbo.[' + @DBTABLENAME + '];';
  end
  else if @PRIMARYKEYTYPENAME = 'bigint'
  begin  
    set @sql =  'select ' + char(13) +
                '  null [ID_UNIQUEIDENTIFIER],' + char(13) +
                '  cast(null as nvarchar(max)) [ID_NVARCHAR],' + char(13) +
                '  null [ID_INT],' + char(13) +
                '  [ID] [ID_BIGINT]' + char(13) + 
                'from dbo.[' + @DBTABLENAME + '];';
  end 

  exec sp_executesql @SQL;