Is there anything wrong with 2 queries

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 2005 Forums

Transact-SQL (2005)

Is there anything wrong with 2 queries

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2009-02-06 : 16:43:51

I have this in my SP, both queries should get executed one after the other.

But for some reason just the first part is OK, the second update statement nothing happening.

IF @ErrorCode = 0

BEGIN

      BEGIN TRANSACTION

 

INSERT INTO TAB_elctr

      (divisionid,

      progid,

      projid,

      phase_id,

      element_id,

      contractid,

      BudgetAmt,

      Updatedby)  

VALUES

      (@divisionid,                             

      @progid,

      @projid,

      @phase_id,

      @element_id,

      @contractid,

      @BudgetAmt,

      @Updatedby)

 

      Update TAB_Ctr set phaseid=@phase_id where contractid=@contractid

 

 

      SELECT @ErrorCode = @@Error

      IF @ErrorCode <> 0

      BEGIN

            ROLLBACK TRANSACTION

            SET NOCOUNT OFF

            RETURN @ErrorCode

      END

END

Thank you very much for the helpful information.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-02-06 : 17:24:04

show us more this can;t be your sp, where are the parms and where are the values @ coming from?

rajendra_dixit
Starting Member

1 Post

Posted - 2009-02-06 : 19:51:56

In Your insert statement column name is phase_id, whereas in update it is phaseid. Undersocre is missing, correct your column name :)

quote:

Originally posted by cplusplus

I have this in my SP, both queries should get executed one after the other.

But for some reason just the first part is OK, the second update statement nothing happening.

IF @ErrorCode = 0

BEGIN

      BEGIN TRANSACTION

 

INSERT INTO TAB_elctr

      (divisionid,

      progid,

      projid,

      phase_id,

      element_id,

      contractid,

      BudgetAmt,

      Updatedby)  

VALUES

      (@divisionid,                             

      @progid,

      @projid,

      @phase_id,

      @element_id,

      @contractid,

      @BudgetAmt,

      @Updatedby)

 

      Update TAB_Ctr set phaseid=@phase_id where contractid=@contractid

 

 

      SELECT @ErrorCode = @@Error

      IF @ErrorCode <> 0

      BEGIN

            ROLLBACK TRANSACTION

            SET NOCOUNT OFF

            RETURN @ErrorCode

      END

END

Thank you very much for the helpful information.

jbp_j
Starting Member

24 Posts

Posted - 2009-02-07 : 01:51:03

HI,

where r u committing ur transaction.

if no errors occurs u have to commit the transaction.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-07 : 08:18:18

please elaborate on this. are you getting any errors? or is it that update affects 0 rows? Also are you doing this in sp? if yes, how will you be passing value directly in query analyser or appln?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources