asked on

Use parameter value as database in from clause?

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

ASKER CERTIFIED SOLUTION

Alpesh Patel

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anuj

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'

deanmachine333

ASKER

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)