in dynamic sql , creating temp table in with in dynamic sql is good or it should be out side.

• rajemessage 14195

  Hall of Fame

  Points: 3315

  More actions

  July 3, 2016 at 1:15 am

  #323667

  hi,

  i have one dynamic sql and in need one temp table like

  declare @db varchar(200)

  declare @tablename varchar(200)

  declare @sql varchar(max)

  @sql='create table @temp1(a int null)

  insert into @temp1(a)

  select abcid from '+@db+@tablename +'

  .........

  .........

  '

  exec(@sql)

  Q1) i can declare temp table out side also , so please tel me in this situation where i need the table in side the dyanmic @sql , will it be any benefite in declareing outside or inside?

  yours sincerely

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  July 3, 2016 at 8:12 am

  #1888219

  If the structure of the temp table is fixed I always declare it outside the dynamic sql simply because then if I need to use it outside I know it will be available.

  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service

• Jeff Moden

  SSC Guru

  Points: 1004436

  More actions

  July 3, 2016 at 10:46 pm

  #1888241

  rajemessage 14195 (7/3/2016)

  ---

  hi,

  i have one dynamic sql and in need one temp table like

  declare @db varchar(200)

  declare @tablename varchar(200)

  declare @sql varchar(max)

  [font="Arial Black"]@sql='create table @temp1(a int null)[/font]

  insert into @temp1(a)

  select abcid from '+@db+@tablename +'

  .........

  .........

  '

  exec(@sql)

  Q1) i can declare temp table out side also , so please tel me in this situation where i need the table in side the dyanmic @sql , will it be any benefite in declareing outside or inside?

  yours sincerely

  Either way, that's not how you create a Temp Table. Also, if it IS supposed to be a Temp Table, you don't need to name it dynamically for any reason what so ever because even if a 1,000 different session all tried to create the same named Temp Table, they would ALL be uniquely named because of the hidden extension added to every one.

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