USP_KPI_SELECTIONCOUNT_WITHASOF

Used to count the number of records in a selection, using an ASOF date as a point of reference for selections that are defined with relative dates.

Parameters

Parameter	Parameter Type	Mode
@SELECTIONID	uniqueidentifier	IN
@ASOFDATE	datetime	IN
@TOTAL	int	INOUT

Definition

Copy




create procedure [dbo].[USP_KPI_SELECTIONCOUNT_WITHASOF] 
@SELECTIONID uniqueidentifier,
@ASOFDATE datetime =null, 
@TOTAL int =0 output

with execute as owner as    

set nocount on;

if @ASOFDATE is null set @ASOFDATE=getdate();

if @SELECTIONID is null
begin
    RAISERROR('@SELECTIONID is required.',16,10);
    return 10;
end

if not exists(select ID from dbo.IDSETREGISTER where ID = @SELECTIONID)
BEGIN 
    raiserror('ID set does not exist in the database.', 16, 11);
    RETURN 11;
END

declare @DBOBJECTNAME nvarchar(128);
declare @DBOBJECTTYPE smallint;
declare @SQLTOEXEC nvarchar(max);
declare @USEASOFDATE bit;

select 
@USEASOFDATE = case DBOBJECTNAMEWITHASOFDATE when '' then 0 else 1 end, 
@DBOBJECTNAME = case DBOBJECTNAMEWITHASOFDATE when '' then DBOBJECTNAME else DBOBJECTNAMEWITHASOFDATE end,
@DBOBJECTTYPE = OBJECTTYPE 
from dbo.IDSETREGISTER where ID = @SELECTIONID;


if @DBOBJECTTYPE = 1
    begin
        set @DBOBJECTNAME = @DBOBJECTNAME + '(';
        if @USEASOFDATE = 1 set @DBOBJECTNAME = @DBOBJECTNAME + '@ASOFDATE';
        set @DBOBJECTNAME = @DBOBJECTNAME + ')';
    end;
else 
    if @DBOBJECTTYPE = 2 set @DBOBJECTNAME = @DBOBJECTNAME + '(''' + convert(nvarchar(36), @SELECTIONID) + ''')';

set @SQLTOEXEC = N'
select @TOTAL = count(*)
from 
dbo.' + @DBOBJECTNAME + ';'

exec sp_executesql @SQLTOEXEC, N'@ASOFDATE datetime, @TOTAL int output',@ASOFDATE=@ASOFDATE,@TOTAL=@TOTAL output;