Stored procedure

Friend,

I tried to take a look at this for you, but I'm afraid your requirements aren't very clear.

In "rule 2," you state not to update Table 2 if appStatus for ReqID 1 is 0, but does that apply to all three records in Table2?

Also, you state that if you're ready to update the record, you want to make sure the user provided date is less than the previous record's date, but you don't specify what you want to do if it isn't.

If you could provide your rules more in terms of what this application is doing it might help. It sounds like you're trying to provide an update status to a work item in a list, and want to make sure that update date isn't prior to any already-completed work items, and also to disallow updates to any records if the status of the entry in Table1 is 1 (presumably indicating the item is closed).

If that's a reasonable understanding of what your rules are, let me know and we'll try to go from there.

-David

update Products set UnitsOnOrder =2 where CategoryID = (Select CategoryID from Categories where CategoryID =1)

hi PatelAlpesh,
could you please provide more details and full code?

thanks

I was trying to help you. But seems to be confusing. Anyway posting what I have now
Just go through this code, and check whether you get any idea.

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

create table #Table1
(
	ReqId	int,
	[desc]	varchar(20),
	status bit
)
insert into #Table1
select 1,	'some text', 	0 union all
select 2,	'some text',	0

create table #Table2
(
	Id				int,
	ReqId			int,
	shopId			char(1),
	appStatus		bit,
	sequence		int,
	CompletionDate	datetime
)
insert into #Table2
select 1,	1,		'W',	0,		1,		NULL union all
select 2,	1,		'M',	0,		2,		NULL union all	
select 3,	1,		'C',	0,		3,		NULL union all
select 4,	2,		'W',	0,		1,		NULL

select * from #Table1
select * from #Table2

select * from #Table1 t1 inner join #Table2 t2 on t1.ReqId = t2.ReqId

-- final query
update t2
	set appStatus = 1 --I want to update the appStatus to 1 for the id=2. 
from #Table1 t1 inner join #Table2 t2 on t1.ReqId = t2.ReqId
where t2.Id = 2  --I want to update the appStatus to 1 for the id=2. 
	and (t1.status <> 1 and t1.ReqId = 1) --1. Table1.status is not 1 for ReqId 1 and appStatus is 1 for Id 1
	and (t2.appStatus = 1 and t2.Id = 1)

-- user provides ReqId = 1, and Id=2 and comletionDate

--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 and appStatus is 1 for Id 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 for ReqId 1.  
--	No need to update Table2 if appStatus is 0 for the ReqId 1
--3. If the two above condition meets and the record is ready for update, then I want check the user provided date is not less than the 
--	previous record's (less sequence record) completion date.

-- Please help.

drop table #Table1
drop table #Table2

Select allOpen in new window

Raj

You can try following code and can modify it according to you. If u find any problem with code let me know.


DECLARE @ReqId INT
DECLARE @Id INT
DECLARE @CompletionDate DATETIME
DECLARE @PrevSeqNo INT
DECLARE @CurrentSeqNo INT
DECLARE @PrevAppStatus BIT
DECLARE @PreviousCompletionDate DATETIME
DECLARE @Status bit

set @ReqId =1
set @Id =2
set @CompletionDate = GETDATE()

/*Initializing variable*/
SET @Status = 0
set @PrevSeqNo  = 0
set @CurrentSeqNo = 0

/*Getting Status from Table1*/
SELECT @Status=status FROM Table1 WHERE reqid = @ReqId


/*Getting current and previous sequence for given Reqid and Id */
SELECT @CurrentSeqNo=sequence, @PrevSeqNo = sequence-1
FROM Table2 WHERE ReqId =@ReqId AND Id = @Id

/*We can update table now*/
IF(@CurrentSeqNo != 0)
begin

      IF(@CurrentSeqNo = 1)
      BEGIN
            UPDATE Table2 set appStatus = 1
            WHERE  Id = @Id AND ReqId =@ReqId AND @Status = 0
      END
      /*Multiple entries for Reqid in Table2*/
      ELSE
      BEGIN

            SELECT @PrevAppStatus= appStatus,  @PreviousCompletionDate= CompletionDate
            FROM Table2 WHERE ReqId =@ReqId AND sequence = @PrevSeqNo

            UPDATE   Table2    SET   appStatus = 1
            WHERE  Id = @Id AND ReqId =@ReqId AND @PrevAppStatus = 1
            AND @PreviousCompletionDate < @CompletionDate      
            
      END
end

Hi anillucky31,

I tried the SP but no result. Even if condition meets no update and no error too.

no need to update multiple tables anyway. /*Multiple entries for Reqid in Table2*/

ALTER PROCEDURE updateAssignmnet

AS
BEGIN

DECLARE @ReqId INT
DECLARE @Id INT
DECLARE @CompletionDate DATETIME
DECLARE @PrevSeqNo INT
DECLARE @CurrentSeqNo INT
DECLARE @PrevAppStatus BIT
DECLARE @PreviousCompletionDate DATETIME
DECLARE @Status bit

set @ReqId =37041
set @Id = 134
set @CompletionDate = GETDATE()

/*Initializing variable*/
SET @Status = 0
set @PrevSeqNo  = 0
set @CurrentSeqNo = 0

/*Getting Status from Table1*/
SELECT @Status=CustService.pickstatus FROM CustService WHERE RequestId = @ReqId 

/*Getting current and previous sequence for given Reqid and Id */
SELECT @CurrentSeqNo=Assignment.worksequence, @PrevSeqNo = Assignment.worksequence-1 
FROM Assignment WHERE Assignment.RequestId =@ReqId

/*We can update table now*/
IF(@CurrentSeqNo != 0)
begin

      IF(@CurrentSeqNo = 1)
      BEGIN
            UPDATE Assignment set status = 1 WHERE  AssignId = @Id AND RequestId =@ReqId AND @Status = 0
      END
      /*Multiple entries for Reqid in Table2*/
      ELSE
      BEGIN

            SELECT @PrevAppStatus= status,  @PreviousCompletionDate= compDate 
            FROM Assignment WHERE RequestId =@ReqId AND workSequence = @PrevSeqNo

            UPDATE   Assignment    SET   status = 1 
            WHERE  AssignId = @Id AND RequestId =@ReqId AND @PrevAppStatus = 1 
            AND @PreviousCompletionDate < @CompletionDate      
            
      END
end
END

Select allOpen in new window

The abvoe actual code I tried.

ReqId = RequestId
Id = AssignId
Table1 = CustService
Table2 = Assignment
CompletionDate = compdate
status = pickstatus
appstatus = staus

ayha

Hi ayha

can you share sample data for your tables for  @ReqId =37041, @Id = 134 and @completiondate?

AssignId      RequestId      compDate      Status      worksequence
133      37041                     NULL            0      1
134      37041            NULL            0      2

AssignId	RequestId	compDate	Status	worksequence
133	37041	         	NULL	0	1
134	37041		NULL	0	2

Select allOpen in new window

sorry when I removed some columns null came in the plance of status

AssignId	RequestId	compDate	Status	worksequence
133	37041	         	0	1
134	37041		0	2

Select allOpen in new window

compDateis null

This code will work fine

 
 
 CREATE PROCEDURE updateAssignmnet
 

BEGIN

DECLARE @RequestId       INT
DECLARE @AssignId       INT
DECLARE @compDate       DATETIME
DECLARE @Prevworksequence INT
DECLARE @Currentworksequence INT
DECLARE @PrevStatus BIT
DECLARE @PreviouscompDate DATETIME
DECLARE @pickstatus bit

set @RequestId =37041
set @AssignId = 134
set @compDate = GETDATE()

/*Initializing variable*/
SET @pickstatus = 0
set @Prevworksequence  = 0
set @Currentworksequence = 0

/*Getting Status from Table1*/
SELECT @pickstatus=CustService.pickstatus FROM CustService WHERE RequestId = @RequestId

PRINT @pickstatus

/*Getting current and previous sequence for given Reqid and Id */
SELECT @Currentworksequence=Assignment.worksequence, @Prevworksequence = Assignment.worksequence-1
FROM Assignment WHERE Assignment.RequestId =@RequestId AND Assignment.AssignId = @AssignId

/*We can update table now*/
IF(@Currentworksequence != 0)
begin

      IF(@Currentworksequence = 1)
      BEGIN
            UPDATE Assignment set status = 1, compDate = @compDate WHERE  AssignId = @AssignId AND RequestId =@RequestId AND @pickstatus = 0
      END
      /*Multiple entries for Reqid in Table2*/
      ELSE
      BEGIN

            SELECT @PrevStatus= status,  @PreviouscompDate= compDate
            FROM Assignment WHERE RequestId =@RequestId AND workSequence = @Prevworksequence

            UPDATE   Assignment    SET   status = 1, compDate = @compDate
            WHERE  AssignId = @AssignId AND RequestId =@RequestId AND @PrevStatus = 1
            AND @PreviouscompDate < @compDate      
           
      END
end
END


If you will pass @RequestId =37041
 @AssignId = 134
@compDate = GETDATE()

nothing will get updated as Status       = 0 for Assignid =133 is not updated in Assignment  table. Once AssignId=133 is updated then AssignId =134 can be updated according to rules provided by you. Please have a look in to above code and try to execute if it meets ur requirements.

Ok.it is working now but there is small problem. Supporse I updated 133 and if I update again, no need update becasuse it will update the compDate. And also pickStatus =1 no need to update any record regardless of the status in the assignment table.

Thanks

ayha

i have modified code. this should work for you

CREATE PROCEDURE updateAssignmnet
 

BEGIN

DECLARE @RequestId       INT
DECLARE @AssignId       INT
DECLARE @compDate       DATETIME
DECLARE @Prevworksequence INT
DECLARE @Currentworksequence INT
DECLARE @PrevStatus BIT
DECLARE @PreviouscompDate DATETIME
DECLARE @pickstatus bit

set @RequestId =37041
set @AssignId = 134
set @compDate = GETDATE()

/*Initializing variable*/
SET @pickstatus = 0
set @Prevworksequence  = 0
set @Currentworksequence = 0

/*Getting Status from Table1*/
SELECT @pickstatus=CustService.pickstatus FROM CustService WHERE RequestId = @RequestId

PRINT @pickstatus

/*Getting current and previous sequence for given Reqid and Id */
SELECT @Currentworksequence=Assignment.worksequence, @Prevworksequence = Assignment.worksequence-1
FROM Assignment WHERE Assignment.RequestId =@RequestId AND Assignment.AssignId = @AssignId

/*We can update table now*/
IF(@Currentworksequence != 0 AND @pickstatus = 0)
begin

      IF(@Currentworksequence = 1)
      BEGIN
            UPDATE Assignment set status = 1, compDate = @compDate WHERE  AssignId = @AssignId AND RequestId =@RequestId AND @pickstatus = 0 and  status  = 0  
      END
      /*Multiple entries for Reqid in Table2*/
      ELSE
      BEGIN

            SELECT @PrevStatus= status,  @PreviouscompDate= compDate
            FROM Assignment WHERE RequestId =@RequestId AND workSequence = @Prevworksequence

            UPDATE   Assignment    SET   status = 1, compDate = @compDate
            WHERE  AssignId = @AssignId AND RequestId =@RequestId AND @PrevStatus = 1
            AND @PreviouscompDate < @compDate   AND @pickstatus = 0 and  status  = 0  
           
      END
end
END

it works but if I update again it updates again, means it updates the compdate which will give wrong result.

thanks

ayha

it wont update again as i have added where condition status  = 0. So it will update only once. So there are no possibility of updating it twice.

I updated the first time I got the comletion time 05/02/2011 10:40:00 PM. when I updated after 1 min. I got 05/02/2011 10:41:00 PM.

ayha

there is no double update now.

set @RequestId =37041
set @AssignId = 134
set @compDate = GETDATE()

when I set the '05/03/2011', the same date of the previous date, it doesn't work. only need to check the second record's date is less than the first record's date. no problem with the equal date.

thanks

ayha

Thanks a lot for your time and suport. Increased the points to maximum.

Thanks once again.

ayha