Update is not working in SP - SQL Server Forums

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)

Update is not working in SP

Author

Topic

socketocp
Starting Member

3 Posts

Posted - 2005-11-11 : 06:24:28

Hi,

I am trying to insert new record in table_b, and run an update statement on table_a, in the same SP.

insert statement is working fine, however, update statement is not updating...

Moreover, the record in table_b is commited however, in table_a, the status field is not been updated....

here is my code...

CREATE PROCEDURE Sub_new
@SubId Int,
AS

SET NOCOUNT ON
DECLARE @verror int
DECLARE @Result int
DECLARE @AId Int

BEGIN TRAN

INSERT INTO [table_b]
(
same
)
VALUES
(
'data'
)

SELECT @verror = @@ERROR, @Result = @@ROWCOUNT

IF @verror <> 0
GOTO on_error
IF @Result > 0
BEGIN

UPDATE table_a
SET status = 1
WHERE id = @SubId

SELECT @verror = @@ERROR, @Result = @@ROWCOUNT

IF @@ERROR <> 0 GOTO on_error

COMMIT TRANSACTION

RETURN @AId
END
ELSE
BEGIN
SELECT @Result = -1
ROLLBACK TRANSACTION
RETURN @Result
END
on_error:
SELECT @Result = -1
ROLLBACK TRANSACTION
RETURN

Kristen
Test

22859 Posts

Posted - 2005-11-11 : 09:20:39

UPDATE table_a
SET status = 1
WHERE id = @SubId

If no record exists for

id = @SubId

then you will have a "success" but zero rows updated. However, you are not checking for zero rows updated, so it won't rollback.

But maybe that's too simple and you DO have a record for id = @SubId ?

Your RETURN is a little loose too - on success you RETURN @AId but @AId is never assigned a value.

on_error: you do a RETURN with no parameter, so the application won't be sure what has happened from the RETURN value.

Kristen

socketocp
Starting Member

3 Posts

Posted - 2005-11-12 : 02:52:36

Thanks for replying..

yeah, i have done, error checking .. moreover, i have taken care the same, which u have pointed out..

However, the status in table_a is not being updated, even though, @SubID exists in table_a.

Kristen
Test

22859 Posts

Posted - 2005-11-12 : 05:57:43

Can't be

Try this, and run it from Query Analyser so you see the output from the Debug PRINT statements:


CREATE PROCEDURE dbo.Sub_new 
	@SubId Int,
AS

SET NOCOUNT ON
DECLARE @verror int
DECLARE @Result int
DECLARE @AId Int

BEGIN TRAN

INSERT INTO dbo.[table_b]
(
	same
)
VALUES
(
	'data'
)

SELECT @verror = @@ERROR, @Result = @@ROWCOUNT
PRINT 'INSERT(1)=' + CONVERT(varchar(20), @Result)

IF @verror <> 0 
	GOTO on_error
IF @Result > 0
BEGIN

	UPDATE dbo.table_a
	SET status = 1
	WHERE id = @SubId
	SELECT @verror = @@ERROR, @Result = @@ROWCOUNT
PRINT 'UPDATE(2)=' + CONVERT(varchar(20), @Result)

	IF @@ERROR <> 0 OR @Result <> 1
		GOTO on_error

PRINT 'COMMIT(3)'
	COMMIT TRANSACTION

PRINT 'RETURN(4)=' + COALESCE(CONVERT(varchar(20), @AId), 'NULL')
	RETURN @AId
END
ELSE
BEGIN
	SELECT @Result = -1
PRINT 'ROLLBACK(5)'
	ROLLBACK TRANSACTION
PRINT 'RETURN(6)=' + COALESCE(CONVERT(varchar(20), @Result), 'NULL')
	RETURN @Result
END

on_error:
SELECT @Result = -1
PRINT 'ROLLBACK(7)'
ROLLBACK TRANSACTION
PRINT 'RETURN(8)=' + COALESCE(CONVERT(varchar(20), @Result), 'NULL')
RETURN @Result

-- Test with:
EXEC dbo.Sub_new @SubId = 1234

Kristen

socketocp
Starting Member

3 Posts

Posted - 2005-11-14 : 03:30:36

Thanks Kristen,

I found that SP had no issues in inserting, and updating. However, the logic, i used in the main function, which was calling this SP, was overwritting the status field, after the update.

Again, thanks for all ur help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources