access variable outside dynamic query

• Anamika

  SSCertifiable

  Points: 6202

  More actions

  June 12, 2009 at 12:09 am

  #155823

  Hi All,

  I want to execute a dynamic query

  DECLARE @TMP2 TABLE (COL_NAMES VARCHAR(255))

  DECLRE @TMP3 VARCHAR(5)

  ,@MySQL VARCHAR(255)

  SET @TMP3 = '@TMP2'

  SET @MySQL = 'INSERT INTO '+ @TMP3+'

  SELECT Column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ='''+@TBL_NAME +''''

  EXEC @MySQL

  I'M getting error like

  Must declare the table variable "@TMP2".

  if i include the declaration of @TMP2 inside @MySQL then it runs but i'm not able to access the table outside the dynamic query.

  how to solve this

  thanks,

  Regards

  Viji

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  June 12, 2009 at 3:17 am

  #1008450

  Try using a temp table instead. Table variables exist only in the scope they're defined in.

  Take a look at this article[/url]: it's an excellent resource for a complete understanding of the topic.

  Regards

  Gianluca

  -- Gianluca Sartori

• Rajesh Patavardhan

  SSCrazy

  Points: 2801

  More actions

  June 12, 2009 at 7:10 am

  #1008594

  Question, just out of curiosity, why the dynamic sql execution required in this scenario as direct sql seems to do the job?

  or am I missing some thing - see below slightly modified code.

  DECLARE @TMP2 TABLE (COL_NAMES VARCHAR(255))

  DECLARE @TMP3 VARCHAR(5),@MySQL VARCHAR(255), @TBL_NAME VARCHAR(255)

  SET @TBL_NAME = 'spt_monitor'

  INSERT INTO @TMP2

  SELECT Column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TBL_NAME

  SELECT * FROM @TMP2

• Anamika

  SSCertifiable

  Points: 6202

  More actions

  June 14, 2009 at 9:08 pm

  #1009458

  Hi,

  i end up with Rajesh's idea. and it works well.

  i have to try Gianluca Sartori's suggestion also.

  thnx guys,

  regards,

  viji

• Jeffrey Williams

  SSC Guru

  Points: 90343

  More actions

  June 14, 2009 at 9:36 pm

  #1009463

  Look up sp_executeSQL in books online. You can input and output variables to a dynamic query executed with the above.

  Jeffrey Williams
  “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

  ― Charles R. Swindoll

  How to post questions to get better answers faster
  Managing Transaction Logs