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.
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'
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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)
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)
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_DET
WHERE [Nominal Code] = 'DT10000'
and [Date] = '2011-08-01'
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_DET
WHERE [Nominal Code] = 'DT10000'
and [Date] = '2011-08-01'
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)
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)
Premium Content
You need an Expert Office subscription to comment.Start Free Trial