@myVar losing value in "exec @myVar = dbo.myStoredProc @parameter"

• Paul_J

  Hall of Fame

  Points: 3698

  More actions

  March 13, 2007 at 11:38 pm

  #116635

  Hi all,

  I have a query I am running where I want to check the value of 2 or more values returned by stored procedures.  Something like:

  exec @var1 = dbo.storedProc1 @param

  exec @var2 = dbo.storedProc2 @param

  exec @var3 = dbo.storedProc3 @param

  (The sprocs return bit values)

  Then I want to examine the values of @var1, @var2, @var3.  But the problem is that after the first stored proc runs, @var1 loses it's value... same for the others...

  What am I not understanding?

  Any help would be appreciated.

  Cheers

• Sergiy

  SSC Guru

  Points: 110192

  More actions

  March 14, 2007 at 1:01 am

  #694860

  1) The way you do it you get error code from SP execution.

  To return something you need to use OUTPUT parameters.

  2) Error code is int, not bit.

  3) The way you posted it there is no way @var1 can lose its value.

  Check its value just after SP execution. Check where it's modified in between.

  _____________
  Code for TallyGenerator

• faizjmc

  SSC Eights!

  Points: 881

  More actions

  March 14, 2007 at 6:10 am

  #694895

  Output keyword is mandatory while executing an sp if the output datatype is other than integer..