Does SQL Server have functionallity like Oracle's %Type Attribute

• OracleWho

  Valued Member

  Points: 65

  More actions

  November 12, 2010 at 12:04 pm

  #388219

  In Oracle, when you declare a variable or parameter in a procedure, you can set the datatype to be the same as a specific column (or even a row) in a table. This is called the %Type (or %RowType) Attribute

  For example, if I have a table that looks like:

  CREATE TABLE employee(

  EmployeeID integer,

  EmployeeName varchar(200))

  I can then declare a variable at

  sEmployeeNameVar employee.EmployeeName%Type

  EmployeeName will be a varchar(200)

  Is there anything in SQL server that gives me the same functionality.

  Thanks!

• GSquared

  SSC Guru

  Points: 260824

  More actions

  November 12, 2010 at 1:59 pm

  #1250253

  Nope. Pretty sure there isn't. Kind of a cool feature.

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

• Jeff Moden

  SSC Guru

  Points: 1004596

  More actions

  November 12, 2010 at 11:07 pm

  #1250331

  OracleWho (11/12/2010)

  ---

  In Oracle, when you declare a variable or parameter in a procedure, you can set the datatype to be the same as a specific column (or even a row) in a table. This is called the %Type (or %RowType) Attribute

  For example, if I have a table that looks like:

  CREATE TABLE employee(

  EmployeeID integer,

  EmployeeName varchar(200))

  I can then declare a variable at

  sEmployeeNameVar employee.EmployeeName%Type

  EmployeeName will be a varchar(200)

  Is there anything in SQL server that gives me the same functionality.

  Thanks!

  I agree. This can't be done in SQL Server in a straight forward manner. Of course, just about anything can be done with dynamic SQL but that's a whole 'nother can of fishing bait that you should probably avoid for such a thing.

  I'll also tell you to not be tempted to use VARCHAR(MAX) for everything because you can create tremendous performance problems owing to implicit conversons.

  --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)
  

• PaulB-TheOneAndOnly

  SSC Guru

  Points: 66772

  More actions

  November 13, 2010 at 6:16 am

  #1250353

  OracleWho (11/12/2010)

  ---

  In Oracle, when you declare a variable or parameter in a procedure, you can set the datatype to be the same as a specific column (or even a row) in a table. This is called the %Type (or %RowType) Attribute

  Is there anything in SQL server that gives me the same functionality.

  As far as I know there is nothing like that SQL Server.

  Oracle TYPE functionality is inherited from ADA, like Oracle packages are. I agree this is a pretty cool feature.

  _____________________________________
  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.