USP_AUDIT_GETDETAIL_HEADER

create procedure dbo.USP_AUDIT_GETDETAIL_HEADER

@AUDITTABLE nvarchar(128),
@AUDITRECORDID uniqueidentifier,
@AUDITKEY uniqueidentifier

with execute as owner

as
--Used by audit trail detail report.
set nocount on;

if @AUDITTABLE like '%;%' or @AUDITTABLE like '%--%' or @AUDITTABLE like '%*%'
    begin
        raiserror('Invalid @AUDITTABLE passed to dbo.USP_AUDIT_GETDETAIL_HEADER',16,10)
        return -12
    end


declare @t table (AUDITDATE datetime,USERNAME nvarchar(128), APPLICATIONNAME nvarchar(255),AUDITTYPECODE tinyint)


declare @sql nvarchar(2000)
set @sql='select top 1 a.AUDITDATE,ca.USERNAME,ca.APPLICATIONNAME, a.AUDITTYPECODE from dbo.' 
+ @AUDITTABLE + 
' as a inner join dbo.CHANGEAGENT as ca on a.AUDITCHANGEAGENTID=ca.id
where a.AUDITRECORDID=''' + cast(@AUDITRECORDID as nvarchar(36)) + '''
and a.AUDITKEY=''' + cast(@AUDITKEY as nvarchar(36)) + ''' and a.AUDITTYPECODE in (0,2)' 


begin try

    declare @transField nvarchar(128);
    declare @baseTable nvarchar(128);
    declare @transValue nvarchar(1024);

    set @transvalue=cast(@AUDITRECORDID as nvarchar(36));

    set @baseTable= left(@AUDITTABLE, len(@AUDITTABLE) - 5);

    select @transField=dbo.UFN_SCHEMA_TABLE_GETTABLETRANSLATIONFIELD(@baseTable);

    if len(coalesce(@transField,''))> 0 
    begin
        declare @transSQL nvarchar(1024);
        declare @parmdef nvarchar(500);

        set @parmdef= N'@id uniqueidentifier, @val nvarchar(1024) output';

        set @transsql='select @val = [' + @transfield + '] from ' + @baseTable + ' where ID = @ID'

        exec sp_executesql @transsql,@parmdef,@ID=@AUDITRECORDID,@val=@transValue output;

    end


    insert into @t (AUDITDATE,USERNAME,APPLICATIONNAME,AUDITTYPECODE)
    exec(@sql)
    select AUDITDATE,USERNAME,APPLICATIONNAME,
        case AUDITTYPECODE when 0 then 'Update' when 2 then 'Delete' else '?' end as CHANGE_TYPE,
        @transvalue as [IDTRANSLATION]
        from @t;        
    return 0;
end try
begin catch
    declare @M nvarchar(2000)
    set @M='Invalid audit table name or other error: ' + ERROR_MESSAGE();
    raiserror(@M,16,10)
    return -44
end catch