declaring global variables

• BeginnerBug

  Hall of Fame

  Points: 3539

  More actions

  October 9, 2010 at 1:59 am

  #387970

  Hi,

  In Oracle, we can set the public variable/constant/types/curosors in the Package specification, Could you please tell me that how to achieve the same functionality in the SQL server.

  Thanks in Advance

• PaulB-TheOneAndOnly

  SSC Guru

  Points: 66772

  More actions

  October 10, 2010 at 6:47 pm

  #1234739

  mail4sha (10/9/2010)

  ---

  In Oracle, we can set the public variable/constant/types/curosors in the Package specification, Could you please tell me that how to achieve the same functionality in the SQL server.

  There is not such a thing as a package in SQL Server but you use DECLARE statement to declare variables, etc. Check here... http://msdn.microsoft.com/en-us/library/aa258839(SQL.80).aspx

  Hope this helps.

  _____________________________________
  Pablo (Paul) Berzukov

  Author of Understanding Database Administration available at Amazon and other bookstores.

  Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

• Vedran Kesegic

  SSCrazy Eights

  Points: 9302

  More actions

  March 7, 2011 at 5:34 pm

  #1295656

  No packages in SQL Server, unfortunately. Packages are one of Oracle advantages over SQL Server (do not start a war, please ).

  But, you have several other options to make data shareable between procedures (beside obvoius: parameters):

  #temporary tables

  ##global temporary tables

  context_info - 128 bytes of connection-scoped data. varbinary(128), initially NULL.

  declare @vb varbinary(128)

  set @vb = context_info() -- read value

  set context_info @vb -- set value

  You can use e.g. one byte of the binary string for one variable, next four bytes for other variable etc.

  Also, @table variables can be input parameters for procedure from sql2008 and up.

  _____________________________________________________
  Microsoft Certified Master: SQL Server 2008
  XDetails Addin - for SQL Developers
  blog.sqlxdetails.com - Transaction log myths