Multiple update doesn't work in my storedprocedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Multiple update doesn't work in my storedprocedure

Author

Topic

Newbie2005
Starting Member

29 Posts

Posted - 2005-07-13 : 17:28:10

I have a stored procedure like this:
CREATE PROCEDURE [dbo].[usp_jobDelete]
(@jobID int) AS
UPDATE Job
SET ItemStateID = 2
WHERE JobID = @jobID

UPDATE JobTask
SET ItemStateID = 2
WHERE JobID = @jobID

UPDATE JobTaskTaskProperty
SET ItemStateID = 2

WHERE EXISTS (SELECT JT.JobID
FROM JobTask AS JT
WHERE JobTaskTaskProperty.JobTaskID = JT.JobTaskID
AND JT.JobID = @jobID)
GO

This one updates first two tables but doesn't update last one. If I move last update to begining then it updates only first table:

CREATE PROCEDURE [dbo].[usp_jobDelete]
(@jobID int) AS

UPDATE JobTaskTaskProperty
SET ItemStateID = 2

WHERE EXISTS (SELECT JT.JobID
FROM JobTask AS JT
WHERE JobTaskTaskProperty.JobTaskID = JT.JobTaskID
AND JT.JobID = @jobID)

UPDATE Job
SET ItemStateID = 2
WHERE JobID = @jobID

UPDATE JobTask
SET ItemStateID = 2
WHERE JobID = @jobID

GO

Please HELP!!!!

jhermiz

3564 Posts

Posted - 2005-07-13 : 17:34:45

quote:
Originally posted by Newbie2005

I have a stored procedure like this:
CREATE PROCEDURE [dbo].[usp_jobDelete]
(@jobID int) AS
UPDATE Job
SET ItemStateID = 2
WHERE JobID = @jobID

UPDATE JobTask
SET ItemStateID = 2
WHERE JobID = @jobID

UPDATE JobTaskTaskProperty
SET ItemStateID = 2

WHERE EXISTS (SELECT JT.JobID
FROM JobTask AS JT
WHERE JobTaskTaskProperty.JobTaskID = JT.JobTaskID
AND JT.JobID = @jobID)
GO

This one updates first two tables but doesn't update last one. If I move last update to begining then it updates only first table:

CREATE PROCEDURE [dbo].[usp_jobDelete]
(@jobID int) AS

UPDATE JobTaskTaskProperty
SET ItemStateID = 2

WHERE EXISTS (SELECT JT.JobID
FROM JobTask AS JT
WHERE JobTaskTaskProperty.JobTaskID = JT.JobTaskID
AND JT.JobID = @jobID)

UPDATE Job
SET ItemStateID = 2
WHERE JobID = @jobID

UPDATE JobTask
SET ItemStateID = 2
WHERE JobID = @jobID

GO

Please HELP!!!!

Not sure why you have that many updates in one sproc, but I don't think that has anything to do with it.

Try this:


CREATE PROCEDURE [dbo].[usp_jobDelete] 
(@jobID int) AS
UPDATE Job 
SET ItemStateID = 2
WHERE JobID = @jobID

UPDATE JobTask 
SET ItemStateID = 2
WHERE JobID = @jobID


IF EXISTS(SELECT JT.JobID 
FROM JobTask AS JT
WHERE JobTaskTaskProperty.JobTaskID = JT.JobTaskID
AND JT.JobID = @jobID) THEN
UPDATE JobTaskTaskProperty
SET ItemStateID = 2
ELSE
GO

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

Newbie2005
Starting Member

29 Posts

Posted - 2005-07-13 : 17:36:46

I receive a syntax error after THEN.

Newbie2005
Starting Member

29 Posts

Posted - 2005-07-13 : 17:52:24

OR How I can call second stored procedure from first one?

First procedure
CREATE PROCEDURE [dbo].[usp_jobPropertyDelete]
(@jobID int) AS

UPDATE JobTaskTaskProperty
SET ItemStateID = 2

WHERE EXISTS (SELECT JT.JobID
FROM JobTask AS JT
WHERE JobTaskTaskProperty.JobTaskID = JT.JobTaskID
AND JT.JobID = @jobID)

Second procedure
CREATE PROCEDURE [dbo].[usp_jobDelete]
(@jobID int) AS

UPDATE Job
SET ItemStateID = 2
WHERE JobID = @jobID

UPDATE JobTask
SET ItemStateID = 2
WHERE JobID = @jobID

GO

jhermiz

3564 Posts

Posted - 2005-07-13 : 22:28:14

quote:
Originally posted by Newbie2005

I receive a syntax error after THEN.

Sorry try this:

CREATE PROCEDURE [dbo].[usp_jobDelete]
(@jobID int) AS
UPDATE Job
SET ItemStateID = 2
WHERE JobID = @jobID

UPDATE JobTask
SET ItemStateID = 2
WHERE JobID = @jobID

IF EXISTS(SELECT JT.JobID
FROM JobTask AS JT
WHERE JobTaskTaskProperty.JobTaskID = JT.JobTaskID
AND JT.JobID = @jobID)
UPDATE JobTaskTaskProperty
SET ItemStateID = 2
ELSE
GO

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

Newbie2005
Starting Member

29 Posts

Posted - 2005-07-14 : 10:46:23

Error 107: The column prefix error JobTaskTaskProperty

I'm receiving error with this way also.

Newbie2005
Starting Member

29 Posts

Posted - 2005-07-14 : 14:59:30

Can anyone give me an idea how I can call second storedprocedure from first one?

First procedure
CREATE PROCEDURE [dbo].[usp_jobPropertyDelete]
(@jobID int) AS

UPDATE JobTaskTaskProperty
SET ItemStateID = 2

WHERE EXISTS (SELECT JT.JobID
FROM JobTask AS JT
WHERE JobTaskTaskProperty.JobTaskID = JT.JobTaskID
AND JT.JobID = @jobID)

Second procedure
CREATE PROCEDURE [dbo].[usp_jobDelete]
(@jobID int) AS

UPDATE Job
SET ItemStateID = 2
WHERE JobID = @jobID

UPDATE JobTask
SET ItemStateID = 2
WHERE JobID = @jobID

GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-14 : 15:12:24

CREATE PROCEDURE [dbo].[usp_jobPropertyDelete]
(@jobID int) AS

UPDATE JobTaskTaskProperty
SET ItemStateID = 2

WHERE EXISTS (SELECT JT.JobID
FROM JobTask AS JT
WHERE JobTaskTaskProperty.JobTaskID = JT.JobTaskID
AND JT.JobID = @jobID)

-- Call second stored procedure
EXEC dbo.usp_jobDelete @jobID

RETURN
GO

Tara

jhermiz

3564 Posts

Posted - 2005-07-14 : 16:14:01

quote:
Originally posted by Newbie2005

Error 107: The column prefix error JobTaskTaskProperty

I'm receiving error with this way also.

You really need to look at why you get these errors, check the name of the table is that correct. look over what you posted are those column names correct.

How about providing the table names (ddl etc) of your structure.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

Newbie2005
Starting Member

29 Posts

Posted - 2005-07-14 : 16:50:51

Tara,

Your example OK to run second st procedure. But same story, first table updated and other three not. If I exchange update queries between st procedures then two tables are updated and last one not. I really confuesed why..

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-14 : 16:52:49

Where are you running your queries from? If it's from your application, then that's not a good idea to do when troubleshooting data issues with your queries. You should be troubleshooting inside Query Analyzer. Run the stored procedure in Query Analyzer. Does it complete successfully? If not, what is the error? If it does, verify the data in the tables using select statements inside Query Analyzer.

Let us know what you find.

Tara

SreenivasBora
Posting Yak Master

164 Posts

Posted - 2005-07-14 : 17:38:40

Hey,

Can you send your table design (If any Triggers on the table(s)).

I want to know is there any reference keys (FK) or any triggers are stopping to update the tables !!!

FYI, before you are updating multiple tables use Transaction control statements and proper error handlers in procedures.

With Regards
BSR

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources