Should i use global variables

• Hatim Ali

  Old Hand

  Points: 375

  More actions

  February 10, 2005 at 4:58 am

  #61657

  Hello,

  Is it appropriate to use global variables such as @@Rowcount or @@Identity?? I've heard that one should avoid global variables because they are updated on last action in any database present on sql server. So should i avoid global variables ??

  What is the alternative of the following T-sql code if i avoid to use @@Rowcount.

  Insert into Table1(a,b) values (1,2)

  if @@Rowcount = 0

      Print 'Failed'

  Else

      Print 'Success'

  Thanks in advance.

  Cheers,

  Hatim Ali.

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  February 10, 2005 at 6:24 am

  #541554

  Here is a good example how to deal safely with these "global variables" in general.

  http://www.sommarskog.se/error-handling-II.html

  http://www.sommarskog.se/error-handling-I.html

  In case if @@IDENTITY you might want to read up BOL for IDENT_CURRENT why @@IDENTITY can get tricky at times.

  In case of @@ROWCOUNT, this example looks good to me.

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

• Edwin Frydendall

  SSCrazy

  Points: 2361

  More actions

  February 10, 2005 at 4:31 pm

  #541702

  Hatim,

  Each connection (or spid) has it's own set of "@@" variables.  You don't have to worry about someone on another connection changing your @@rowcount or @@identity variable.

   

• Jeff Moden

  SSC Guru

  Points: 1004406

  More actions

  February 10, 2005 at 7:54 pm

  #541710

  Don't use @@IDENTITY which can be tricked by triggers... use SCOPE_IDENTITY() instead.

  Always assign values captured in @@ROWCOUNT and @@ERROR immediately after the SQL that you want to check.  You can do both using SELECT instead of SET...

  ...some SQL does something
  SELECT @MyCount = @@ROWCOUNT, @MyError = @@ERROR

  Note the @MyCount and @MyError are just user defined variables.and must be declared at the beginning of the script or proc.

  --Jeff Moden

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)