spDelete_SiteSurveyResponse

        CREATE  procedure [dbo].[spDelete_SiteSurveyResponse](@SiteSurveysId    int)
        as
        begin
                set nocount on
                declare @ClientSitesId integer
                select @ClientSitesId = ClientSitesId
                from SiteSurveys 
                where ID = @SiteSurveysId

                begin tran T1
                delete from SiteSurveyResponses
                where SiteSurveysId = @SiteSurveysId

                if @@error = 0 
                begin
                    if exists(select * from SiteSurveyResponsesDeleteTransactions
                        where ClientSitesId = @ClientSitesId and SiteSurveysId = @SiteSurveysId)
                        update SiteSurveyResponsesDeleteTransactions
                        set DeleteDate = getutcdate()
                        where ClientSitesId = @ClientSitesId and SiteSurveysId = @SiteSurveysId
                    else
                        insert into SiteSurveyResponsesDeleteTransactions(SiteSurveysId, ClientSitesId, DeleteDate)
                        values (@SiteSurveysId, @ClientSitesId, getutcdate())

                    if @@error = 0 
                        commit tran T1
                    else
                        rollback tran T1
                end
                else
                    rollback tran T1
        end