Solved

update stored procedure is not working

Posted on 2013-10-29
14
259 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:AZZA-KHAMEES
Comment Utility
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
Comment Utility
>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
Comment Utility
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
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:AZZA-KHAMEES
Comment Utility
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
Comment Utility
>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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
thank you
0
 
LVL 65

Expert Comment

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

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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.‚Äč
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
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.

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now