Looking for SQL Code

Question

Looking for SQL Code

patla4u

SSCrazy

Points: 2103
More actions
January 29, 2020 at 7:11 pm

#3721342
Hi
I am having situation where I would like to update the existing data before inserting any new record. It records are two in row, update both but if they are not , then I don't need to update again.
In below example, I am inserting data for process ID 3 with process status 2.. But Since FailedNumber is already exist in ProcessTable & His status was 1 and I would like to update his status to 2 for processID 1 & 2 . Once the data updated and inserted,, there is another process that will change status to 1(after another process run).

There is another situation where I am inserting data for peocessID 3.. While inserting the data for process ID 3.. I am looking for Previous Process ID with failedNumber.. Since ProcessID 2 is not exist, I don't want to update process status to 2 for ProcessID = 1
Let me know if you need more explanation.
Thanks
Bhavesh
- This topic was modified 4 years, 11 months ago by patla4u. Reason: Duplicate

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 1

Sounds like

IF (SELECT COUNT(*) 
    FROM   tbl 
    WHERE  FailedNumber = @FailedNumber 
      AND  ProcessID IN (@ProcessID - 1, @ProcessID - 2)
BEGIN
   UPDATE tbl
   SET    status = @status
   WHERE  FailedNumber = @FailedNumber
     AND  ProcessID IN (@ProcessID - 1, @ProcessID - 2)
     AND  Status <> @status
END

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

patla4u SSCrazy Points: 2103 More actions · Answer 2

Thanks.

How can we handle if we have more than 100 records.. This is only for one FailedNumber but looking for multiple failedNumber..

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 3

I can only give a solution to the problem you state, which was for the update of a single value.

Since I don't know how your more than 100 records are packaged, and I've only guessed wrong once, I am not going to make a second guess. Please supply CREATE TABLE + INSERT statements for your table(s) and the sample data.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Steve Collins SSCrazy Points: 2177 More actions · Answer 4

Nevermind

This reply was modified 4 years, 11 months ago by Steve Collins.

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Steve Collins SSCrazy Points: 2177 More actions · Answer 5

Is this really sql 2008?

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Steve Collins SSCrazy Points: 2177 More actions · Answer 6

Not tested on Sql 2008 but it should work afaik.

--drop table if exists dbo.test_process_statuses;
--go
create table dbo.test_process_statuses(
  ProcessKeyId                    int identity(1,1) primary key not null,
  ProcessDate                    date not null,
  FailedNumber                    varchar(5) not null,
  ProcessId                        int not null,
  ProcessStatus                    int not null);
go

/* example #1 */
insert dbo.test_process_statuses(ProcessDate, FailedNumber, ProcessId, ProcessStatus) values
('2020-01-20', 'C1000', 1, 1),
('2020-01-21', 'C1000', 2, 1);

/* example #2 */
insert dbo.test_process_statuses(ProcessDate, FailedNumber, ProcessId, ProcessStatus) values
('2020-01-20', 'C1001', 1, 1);

--drop proc if exists dbo.test_proc_process;
--go
create proc dbo.test_proc_process
  @ProcessDate                    date,
  @FailedNumber                    varchar(5),
  @ProcessId                    int,
  @ProcessStatus                int,
  @ProcessKeyId                    int output
as
set nocount on;
set xact_abort on;

begin transaction
begin try
    /* try to update where the previous 2 process id's exists */
    with
    prev_two_cte(FailedNumber, PrevProcCount) as (
        select 
          FailedNumber,
          count(*) 
        from
          dbo.test_process_statuses
        where
          FailedNumber=@FailedNumber
          and ProcessId in(@ProcessId-1, @ProcessId-2)
        group by
          FailedNumber
        having
          count(*)=2)
    update tps
    set
      ProcessStatus=@ProcessStatus
    from
      dbo.test_process_statuses tps
     join
      prev_two_cte ptc on tps.FailedNumber=ptc.FailedNumber
    where
      ProcessId in(@ProcessId-1, @ProcessId-2);

    /* insert new record */
    insert dbo.test_process_statuses(ProcessDate, FailedNumber, ProcessId, ProcessStatus) values
    (@ProcessDate, @FailedNumber, @ProcessId, @ProcessStatus);
    select @ProcessKeyId=cast(scope_identity() as int);
    
    commit transaction;
end try
begin catch
    /* put error handling here */
    select @ProcessKeyId=0;

    rollback transaction;
end catch

set xact_abort off;
set nocount off;
go

declare
  @ProcKeyId            int;

exec dbo.test_proc_process '2020-01-22', 'C1000', 3, 2, @ProcessKeyId=@ProcKeyId output;
if @ProcKeyId>0
    print ('Sucess');
else
    print ('No bueno');

exec dbo.test_proc_process '2020-01-22', 'C1001', 3, 2, @ProcessKeyId=@ProcKeyId output;
if @ProcKeyId>0
    print ('Sucess');
else
    print ('No bueno');

select * from dbo.test_process_statuses order by 3, 4;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

patla4u SSCrazy Points: 2103 More actions · Answer 7

patla4u

SSCrazy

Points: 2103

January 31, 2020 at 1:47 pm

#3722003

Thanks.