dynamic query

• kumarramachandrarao

  SSC-Addicted

  Points: 479

  More actions

  September 7, 2011 at 4:58 am

  #243501

  hi

  can anyone tell me how i should do it.

  normal way of opening the database is

  use database_name

  select * from xx_table name

  now i have temp table where i have stored the database name

  and i want to open database in dynamic way

  and then use the select statement

  how should i do it

  Thanks in advance

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  September 7, 2011 at 6:03 am

  #1377908

  This should do the trick:

  -- set up temporary table with database name

  CREATE TABLE #databases (

  database_name sysname

  )

  INSERT INTO #databases VALUES ('tempdb')

  -- prepare the statement

  DECLARE @sql nvarchar(max)

  SELECT @sql = 'EXEC ' + QUOTENAME(database_name) + '.sys.sp_executesql @statement'

  FROM #databases

  -- execute from the database's context

  EXEC sp_executesql @sql, N'@statement nvarchar(max)', N'SELECT * FROM xx_table_name'

  Hope this helps

  Gianluca

  -- Gianluca Sartori