USP_OLAP_ETL_DATAMART_GETDELETEDIDS

CREATE procedure dbo.USP_OLAP_ETL_DATAMART_GETDELETEDIDS

@AUDITTABLENAME nvarchar(128),
@DATAWINDOWOPEN datetime,
@DATAWINDOWCLOSE datetime 

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() AS AUDITRECORDID;

declare @ETL_DELETEDID_TABLENAME nvarchar(128) = @AUDITTABLENAME + '_ETLDELETEDID';

if not exists(select [name] from sys.tables t where t.[name] = @AUDITTABLENAME or (t.[name] = @ETL_DELETEDID_TABLENAME and t.[schema_id]=SCHEMA_ID('BBETLAUDIT')))
BEGIN
    RAISERROR('Invalid table name passed to dbo.USP_OLAP_ETL_DATAMART_GETDELETEDIDS',16,10);
    return 100;
END

if @DATAWINDOWOPEN is null
BEGIN
    RAISERROR('Null @DATAWINDOWOPEN passed to dbo.USP_OLAP_ETL_DATAMART_GETDELETEDIDS',16,10);
    return 200;
END

if @DATAWINDOWCLOSE is null
BEGIN
    RAISERROR('Null @DATAWINDOWCLOSE passed to dbo.USP_OLAP_ETL_DATAMART_GETDELETEDIDS',16,10);
    return 300;
END

if @DATAWINDOWCLOSE < @DATAWINDOWOPEN 
BEGIN
    RAISERROR('@DATAWINDOWCLOSE must be later than @DATAWINDOWOPEN in dbo.USP_OLAP_ETL_DATAMART_GETDELETEDIDS',16,10);
    return 400;
END

------------------------------------------------------------------------------------------------------------------------------

/* 
pdg 12.21.2010 
use new BBETLAUDIT deleted id tables if they are present, otherwise fall back to the existing audit tables
*/

if exists(select [name] from sys.tables as t where t.[name] = @ETL_DELETEDID_TABLENAME and t.[schema_id]=SCHEMA_ID('BBETLAUDIT') )
BEGIN
    set @AUDITTABLENAME= 'BBETLAUDIT.' + @ETL_DELETEDID_TABLENAME;
END
------------------------------------------------------------------------------------------------------------------------------




declare @sql nvarchar(1024);
if 1=1 -- required for SSIS

BEGIN
set @sql=
'SELECT 
DISTINCT AUDITRECORDID from ' + @AUDITTABLENAME + '
WHERE
(AUDITDATE > @dwo )

AND 
(AUDITDATE <= @dwc )

AND
(AUDITTYPECODE = 2)
';


END
ELSE
BEGIN
--dummy sql for SSIS FMTONLY statement

set @sql='SELECT NEWID() AS AUDITRECORDID from dbo.STATEAUDIT WHERE GETDATE()=@dwo AND GETDATE()=@dwc AND NULL=1'
END

declare @r int;

exec @r=sp_executesql @sql,N'@dwo datetime,@dwc datetime',@dwo=@DATAWINDOWOPEN, @dwc=@DATAWINDOWCLOSE;

return @r;