Solved

update stored procedure is not working

Posted on 2013-10-29
14
262 Views
Last Modified: 2013-10-29
Hi Experts,
Stored procedure was created, but its not working

BEGIN
	DECLARE @ProcessInstanceId VARCHAR(10)
	DECLARE @incomplete_task_id VARCHAR(10)
	DECLARE @task_complete_time datetime
	DECLARE @process_instance_status  VARCHAR(50)

	SELECT @ProcessInstanceId = id FROM AdobeLCES2_test.dbo.tb_process_instance 
		WHERE id in (
		SELECT ReferenceNo from WorkFlow_test.dbo.tbl_ServiceTracking where status='Completed' or status='Terminated' or status='Rejected' or status='Delivered' or status='Cancel Request')
		and status=1
	
	SELECT @incomplete_task_id = id FROM AdobeLCES2_test.dbo.tb_task 
		WHERE process_instance_id = @ProcessInstanceId  AND status!=100
	
	SELECT @process_instance_status = Status FROM WorkFlow_test.dbo.tbl_ServiceTracking 
		WHERE WorkflowPID = @ProcessInstanceId
		
	UPDATE AdobeLCES2_test.dbo.tb_task SET complete_time = GETDATE(), 
				status = 100 WHERE id = @incomplete_task_id
			
	UPDATE AdobeLCES2_test.dbo.tb_process_instance SET update_time = GETDATE(),
				status = 2 WHERE id = @ProcessInstanceId
				
END

Open in new window


what wrong with it? any suggestion ?
0
Comment
Question by:AZZA-KHAMEES
  • 6
  • 6
  • 2
14 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39608406
Define 'is not working'.  Does it return an error message on a specific line?  Does it execute but not update anything?  Is it supposed to update a set, but only updates a single row?

The syntax appears correct, and our mind-reading skills have some limitations.
0
 

Author Comment

by:AZZA-KHAMEES
ID: 39608417
thank you for the reply, sorry for not mention that, the syntax is correct and execute successfully by its not updating any thing and it should update 102 rows, did i miss any thing?
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 39608420
With my voodoo mindreading skills, I would suggest that this:

SELECT @ProcessInstanceId = id FROM AdobeLCES2_test.dbo.tb_process_instance
            WHERE id in (
            SELECT ReferenceNo from WorkFlow_test.dbo.tbl_ServiceTracking where status='Completed' or status='Terminated' or status='Rejected' or status='Delivered' or status='Cancel Request')
            and status=1

is returning more than one record so is not setting the variable correctly.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:AZZA-KHAMEES
ID: 39608424
yes it return more than one record, about 102 rows, and sorry i am not an expert in sql :(
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39608432
>it should update 102 rows
Looking at the two UPDATE statements I see WHERE ... id = @some_variable, and id usually means a unique identifier, so it appears that each one should only be updating a single row.  Explain why you believe it should update 102 rows.

>sorry i am not an expert in sql :(
That's fine, but you need to be an expert in explaining your requirements.
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 39608458
If this:

SELECT id FROM AdobeLCES2_test.dbo.tb_process_instance
            WHERE id in (
            SELECT ReferenceNo from WorkFlow_test.dbo.tbl_ServiceTracking where status='Completed' or status='Terminated' or status='Rejected' or status='Delivered' or status='Cancel Request')
            and status=1

Returns more than one row, you say 102, then there you cannot store the results of that query of 102 IDs in one variable.
0
 

Author Comment

by:AZZA-KHAMEES
ID: 39608461
sorry, in the first sql statement i need to return all rows available in tbl_ServiceTracking and status = 1
SELECT @ProcessInstanceId = id FROM AdobeLCES2_test.dbo.tb_process_instance 
		WHERE id in (
		SELECT ReferenceNo from WorkFlow_test.dbo.tbl_ServiceTracking where status='Completed' or status='Terminated' or status='Rejected' or status='Delivered' or status='Cancel Request')
		and status=1

Open in new window

this will return about 102 rows

in the second will select all the record from       the first and match it with another table process_instance and the status !=100

	SELECT @incomplete_task_id = id FROM AdobeLCES2_test.dbo.tb_task 
		WHERE process_instance_id = @ProcessInstanceId  AND status!=100

SELECT @process_instance_status = Status FROM WorkFlow_test.dbo.tbl_ServiceTracking 
		WHERE WorkflowPID = @ProcessInstanceId
		

Open in new window

in the last 2 statement will update this records depend on the result form the first 2
      
	
	UPDATE AdobeLCES2_test.dbo.tb_task SET complete_time = GETDATE(), 
				status = 100 WHERE id = @incomplete_task_id
			
	UPDATE AdobeLCES2_test.dbo.tb_process_instance SET update_time = GETDATE(),
				status = 2 WHERE id = @ProcessInstanceId

Open in new window

0
 

Author Comment

by:AZZA-KHAMEES
ID: 39608469
thanks you all for the replies, so lsavidge i need to create an array or table to read from it the result
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39608482
>DECLARE @ProcessInstanceId VARCHAR(10)
>SELECT @ProcessInstanceId = id

The above takes a single id and assigns it to the @ProcessInstanceId (ten-character string) variable, so it is not designed to handle 102 rows.

Looking at the other two SELECTS they similarly have SELECT @some_variable = some_column, so there appears to be nothing in this T-SQL that was built to handle sets of rows.
0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 39608496
Here's an example of an UPDATE statement, using parts of your T-SQL and no scalar (i.e. single value) variables, that can update a set of id's.  I HIGHLY recommend reviewing this to make sure it is correct, and having a backup copy of your data, before running it.
UPDATE AdobeLCES2_test.dbo.tb_task 
SET complete_time = GETDATE(), status = 100 
WHERE id IN (		
   SELECT ReferenceNo 
   from WorkFlow_test.dbo.tbl_ServiceTracking 
   where status IN ('Completed', 'Terminated', 'Rejected', 'Delivered', 'Cancel Request')
   and status=1

Open in new window

0
 

Author Comment

by:AZZA-KHAMEES
ID: 39608542
thank you very much jimhorn for your help, i change my stored procedure to this

DECLARE @ProcessInstanceId table
(
	processno	VARCHAR(10)
)	

DECLARE @incomplete_task_id table
(
	taskid VARCHAR(10)
)
INSERT into @ProcessInstanceId 
SELECT processno = id FROM AdobeLCES2_test.dbo.tb_process_instance 
WHERE id in (
	SELECT ReferenceNo from WorkFlow_test.dbo.tbl_ServiceTracking 
	where status='Completed' or status='Terminated' or status='Rejected' or status='Delivered' or status='Cancel Request')
AND status=1

INSERT into @incomplete_task_id 
SELECT taskid = id FROM AdobeLCES2_test.dbo.tb_task 
		WHERE process_instance_id in (select processno from @ProcessInstanceId)  AND status!=100

UPDATE AdobeLCES2_test.dbo.tb_task SET complete_time = GETDATE(), 
				status = 100 WHERE id in (select taskid from @incomplete_task_id) 
			
UPDATE AdobeLCES2_test.dbo.tb_process_instance SET update_time = GETDATE(),
				status = 2 WHERE id in  (select processno from @ProcessInstanceId)

Open in new window


and for the first 2 query they are working perfectly, but i didnt test the last 2,
is it correct or not?
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39608553
DO NOT INSERT INTO @a_variable if you wish to affect more than one row.

At this point I would recommend enlisting some T-SQL help at your location, as being a beginner in T-SQL may be a limiting factor in getting a solution here.
0
 

Author Closing Comment

by:AZZA-KHAMEES
ID: 39608590
thank you
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39608604
Thanks for the grade.  Good luck with your code.  -Jim
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question