Tuesday, October 4, 2016

Combine Multiple Statements into one Query(SQL Server)



<# tw.local.parameters = new tw.object.listOf.SQLParameter(); #>

declare @wItemId bigint = ?; <# APCAddSqlParameter(tw.local.parameters, tw.local.wItemId); #>
declare @userName nvarchar(255) = ?; <# APCAddSqlParameter(tw.local.parameters, tw.local.userName); #>
declare @historyLength int = 10;

declare @itemId bigint;
declare @roomId bigint;
declare @homeId bigint;

select top 1
    @itemId = ERP.itemId ,
    @roomId = PRJ.roomId ,
    @homeId = ENG.homeId
from WItem2EntityReportingPeriod WI2ERP
    join EntityReportingPeriod ERP on WI2ERP.entityReportingPeriodId = ERP.entityReportingPeriodId
    join Project PRJ on PRJ.roomId = ERP.roomId
    join Engagement ENG on ENG.engagementId = PRJ.engagementId
where WI2ERP.wItemId = @wItemId ;

update UserActivity
set lastUpdated = CURRENT_TIMESTAMP
where userName = @userName
    and itemId = @itemId
    and roomId = @roomId
    and homeId = @homeId ;

if @@ROWCOUNT = 0
begin
    -- delete old rows for user from UserActivity
    with uaDates as (
        select userActivityId
            , row_number() over (order by lastUpdated desc) as rowNumber
        from UserActivity
        where userName = @userName
    )
    delete UserActivity
    from UserActivity UA
        join uaDates on UA.userActivityId = uaDates.userActivityId
    where uaDates.rowNumber >= @historyLength;

    -- add new UserActivity
    insert into UserActivity (userActivityId, userName, homeId , roomId , itemId , lastUpdated)
    values (next value for UserActivity_Seq, @userName, @homeId , @roomId , @itemId , CURRENT_TIMESTAMP);
end

No comments:

Post a Comment