• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 290
  • Last Modified:

Update procedure

I have the following tables and data. How can I create SP to achieve the following result?

Table1
ReqId(PK)   desc               status(bit)
1                    some text      0
2                    some text      0

Table2
Id(PK)      ReqId(FK)      shopId      appStatus(bit)      sequence (int)
1      1            W      0            1
2      1            M      0            2
3      1            C      0            3
4      2            W      0            1

user provides ReqId = 1 and Id=2

I want to update the appStatus to 1 for the id=2.
Before updating the table I want make surethe following
1. Table1.status is not 1 for ReqId 1
2. if a record has less 'sequence' for ReqId 1 then I want to make sure that the less 'sequence' record's appStatus is 1. allow to update appStatus to 1 only after the previous record's (less sequence) being updated.

Please help.

ayha
0
ayha1999
Asked:
ayha1999
  • 8
  • 4
  • 2
2 Solutions
 
Pratima PharandeCommented:
Say user provides @ID and @reqId
If Exists ( Select * from Table1 Where status <> 1 and ReqId = @ReqId)
Begin

If Exists ( select * from Table2 where Id =(@id-1) )
begin
Update Table2
set appStatus = 1
where ReqId = 1 and Id= 2  and appStatus =1
end
else
begin
Update Table2
set appStatus = 1
where ReqId = 1 and Id= 2
end

End
0
 
ayha1999Author Commented:
could u pls explain "If Exists ( select * from Table2 where Id =(@id-1) )"?
0
 
karthitronCommented:
Hi,

If user provides Id, then no need to provide ReqId

Using ID,
1. get the ReqID.
2. Update Table2 only if respective record in Table1.status is not equal to 1.
3. Update Table2.Status whose sequce of respective ReqId is less than current ID's Status (I think, here we should check whether lesser sequence's appStatus is 1 otherwise we should not update current record, but you have asked to update appStatus of lesser sequence along with current record, if that is the case, we can blindly update all the appStatus =1 for sequence lesser or equal to current sequcne instead of lesser sequence. Anyhow, following is my sample script as per your requirement)
4. Then finally you can update Table2.Status for ID
CREATE PROCEDURE test
	@Id int
AS
BEGIN
	SET NOCOUNT ON;

	IF EXISTS(SELECT Id FROM Table1 WHERE Id = @ID)
	BEGIN
		DECLARE @ReqID int, @Sequence int;
		SELECT @ReqID = T2.ReqId, @Sequence = T2.Sequence FROM Table1 T1 INNER JOIN Table2 T2 on T1.ReqId = T2.ReqId WHERE T1.Id = @ID AND T1.appStatus <> 1
		IF @ReqID IS NULL
		PRINT 'No need to update / invalid data'
		ELSE
		BEGIN
			UPDATE Table2 SET appStatus = 1 WHERE Sequence <= @Sequence AND ReqID = @ReqID
		END
	END
END
GO

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
ayha1999Author Commented:
"but you have asked to update appStatus of lesser sequence along with current record" this is my mistake. no need to upadate the current record if thtat's the case.
0
 
ayha1999Author Commented:
also please incldue this condition "Table1.status is not 1 for ReqId 1"
0
 
ayha1999Author Commented:
here is 'sequence' no is used to make sure the order. a record with sequence 2 cannot be updated if the record with sequence is not updated (appstatus)
0
 
karthitronCommented:
Ok, I have already included the condition "Table1.status is not 1 for ReqId 1"  as
T1.appStatus <> 1
in the line 10.

If you dont want to update current record then update the line 15 as :
UPDATE Table2 SET appStatus = 1 WHERE Sequence < @Sequence AND ReqID = @ReqID  AND Id <> @Id
0
 
Pratima PharandeCommented:
If Exists ( select * from Table2 where Id =(@id-1) )

this is to check the lesser sequance
0
 
karthitronCommented:
Earlier you asked to update appStatus as 1 for lesser sequence numbers. Now what you are asking, I cant able to understand "a record with sequence 2 cannot be updated if the record with sequence is not updated (appstatus) ". I understand that sequence number is to store the order, other than that I can't understand your correct requirement.
0
 
ayha1999Author Commented:
If Exists ( select * from Table2 where Id =(@id-1) )

this is not checking the sequence no.

before updating the record (with provided id) it has check that if any record exists with status 0 for provided reqid. if so no need to update else update the record.

i will my actual data I tried.

ALTER PROCEDURE dbo.updateStatus
(@ReqId int, @AssignId int)
AS
	If Exists (Select * from CustService Where pickstatus <> 1 and CustService.RequestId = @ReqId)
Begin

If Exists ( select * from Assignment where AssignId =(@AssignId-1) and Assignment.RequestId=@ReqId)
begin
Update Assignment
set Status = 1
where AssignId= @AssignId
end

End 
RETURN

Open in new window

0
 
ayha1999Author Commented:
id = AssignId
reqId = RequestId
Table1 = custService
Table2 = Assignment
status=pickstatus
appstatus=status
0
 
karthitronCommented:
why are you using
AssignId =(@AssignId-1)

AssignId is a auto generated Id know, it is not a sequence number to check lesser sequence.
0
 
ayha1999Author Commented:
yes. but I was just trying his solution first. I think you are confused with table structure. I make some corrections in the your code and executed. I tried to update the second record (seq. 2) then it just updated the status without checking the lesser seq. record. the second record should not get udpated bec. a lesser seq. recrod is there with status 0.
0
 
ayha1999Author Commented:
The solution isnot complete.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now