Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

update stored procedure is not working

Posted on 2013-10-29
14
Medium Priority
?
275 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
  • 2
14 Comments
 
LVL 66

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
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 

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 66

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 66

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 66

Accepted Solution

by:
Jim Horn earned 2000 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 66

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 66

Expert Comment

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

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

722 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