Use parameter value as database in from clause?

deanmachine333
deanmachine333 used Ask the Experts™
on
Hello guys,

I have to create a report to be able to use a parameter value in the from clause , the parameter is a database followed by table name eg in code section.


@dbase varchar(100)

SELECT [@DBASE] as [DBASE]
,[DET_NOMINALDR] as [Nominal Code]
,[DET_DATE] as [Date]
,[DET_PERIODNUMBR] as [Period]
,[DET_YEAR] as [Year]
,[DET_DESCRIPTION] as [Narrative]
,[DET_NETT] as [D/C]

FROM @dbase.[dbo].[SL_PL_NL_DETAIL]
WHERE [Nominal Code] = 'DT10000'
  and [Date] = '2011-08-01'

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Solution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
Commented:
To do that use Inline Query.

SeT @STR ='SELECT [@DBASE] as [DBASE]
,[DET_NOMINALDR] as [Nominal Code]
,[DET_DATE] as [Date]
,[DET_PERIODNUMBR] as [Period]
,[DET_YEAR] as [Year]
,[DET_DESCRIPTION] as [Narrative]
,[DET_NETT] as [D/C]

FROM ' + @dbase + '.[dbo].[SL_PL_NL_DETAIL]
WHERE [Nominal Code] = 'DT10000'
  and [Date] = ''2011-08-01''''

EXEC (@STR)
AnujSQL Server DBA
Top Expert 2011

Commented:
Like this ?

SELECT [@DBASE] = DB_ID() as [DBASE]
,[DET_NOMINALDR] as [Nominal Code]
,[DET_DATE] as [Date]
,[DET_PERIODNUMBR] as [Period]
,[DET_YEAR] as [Year]
,[DET_DESCRIPTION] as [Narrative]
,[DET_NETT] as [D/C]

FROM @dbase.[dbo].[SL_PL_NL_DETAIL]
WHERE [Nominal Code] = 'DT10000'
  and [Date] = '2011-08-01'

Author

Commented:
Worked but needed to add extra '' in the where clause for nominal clause and delete one ' by the date clause and also changed [@dbase] to '''+@dbase+''' so should look like this in full

declare @dbase varchar(150) = 'testdatabase'
declare @STR varchar(max)

SeT @STR ='SELECT '''+@dbase+''' as [DBASE]
                        ,[DET_NOMINALDR] as [Nominal Code]
                        ,[DET_DATE] as [Date]
                        ,[DET_PERIODNUMBR] as [Period]
                        ,[DET_YEAR] as [Year]
                        ,[DET_DESCRIPTION] as [Narrative]
                        ,[DET_NETT] as [D/C]

FROM ' + @dbase + '.[dbo].[SL_PL_NL_DETAIL]
WHERE [DET_NOMINALDR] = ''DT10150''
  and [DET_PERIODNUMBR] = 8
  and [DET_YEAR] = ''C'''
 
EXEC (@STR)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial