Get Table by name

• barakos

  Valued Member

  Points: 63

  More actions

  February 14, 2011 at 2:03 am

  #234945

  HI.

  I want to ask if there's a way to call a function with the argument "table name" (string),

  and the function will return a variable of type Table?

  Thanks.

• k.thanigaivel

  Ten Centuries

  Points: 1247

  More actions

  February 14, 2011 at 2:06 am

  #1286336

  use sp_executesql it will help you.

• Kingston Dhasian

  SSCoach

  Points: 19799

  More actions

  February 14, 2011 at 2:09 am

  #1286337

  barakos (2/14/2011)

  ---

  HI.

  I want to ask if there's a way to call a function with the argument "table name" (string),

  and the function will return a variable of type Table?

  Thanks.

  I think a Table Valued Function is what you are looking for. You can get more information on the same in Books Online or Google..

  ---

  Kingston Dhasian

  How to post data/code on a forum to get the best help - Jeff Moden
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

• Orlando Colamatteo

  SSC Guru

  Points: 182279

  More actions

  February 14, 2011 at 1:47 pm

  #1286737

  barakos,

  I am guessing you have an Oracle background and are looking for something similar to the %ROW_TYPE functionality but it does not exist in SQL Server.

  There is an open item requesting that functions like this be added to the product (which I voted in favor of) but it's been open for quite a while:

  https://connect.microsoft.com/SQLServer/feedback/details/124506

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  February 14, 2011 at 2:52 pm

  #1286765

  In answer to your question .. Yes there is ... here is an example posted the other day here on SSC at:

  http://www.sqlservercentral.com/Forums/Topic1062357-338-1.aspx#bm1063021

  by the user with the name of: mister.magoo

  CREATE FUNCTION OrdinalDateSuffix( @InputDate DATETIME )

  RETURNS TABLE

  AS

  RETURN ( WITH special(DAY,suffix)

  AS

  (

  SELECT 1, 'st' UNION ALL

  SELECT 2, 'nd' UNION ALL

  SELECT 3, 'rd' UNION ALL

  SELECT 21, 'st' UNION ALL

  SELECT 22, 'nd' UNION ALL

  SELECT 23, 'rd' UNION ALL

  SELECT 31, 'st'

  )

  SELECT

  CONVERT (NVARCHAR(2), DATEPART (DAY, @InputDate)) +

  COALESCE (

  (SELECT

  suffix

  FROM

  special

  WHERE

  DAY = DATEPART(DAY,@InputDate)

  ),'th') AS suffix

  )

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]

• Jeff Moden

  SSC Guru

  Points: 1004406

  More actions

  February 14, 2011 at 4:33 pm

  #1286798

  k.thanigaivel (2/14/2011)

  ---

  use sp_executesql it will help you.

  I'm fairly certain that you cannot use sp_executesql in a function, if that's what you meant. The only thing you can exec in a UDF is an extended (xp_*) stored procedure.

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