Hiya don't know if the title is correct but this is my code -
I'm trying to add parameters in the where clause of this query, i have successfully managed to add the database parameter but cant seem to get the where clause to work with parameters. I don't really want to run the where clause outside of this as takes longer when putting results into tmp table then using parameters.
if OBJECT_ID('tempdb..##tmpUserDBase') IS NOT NULL
declare @dbase varchar(150) = 'testdbase'
declare @ncode varchar(25) = 'DT10150'
declare @period varchar(10) = 8
declare @year varchar(10) = 'C'
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] = ''@ncode''
and [DET_PERIODNUMBR] = ''@period''
and [DET_YEAR] = ''@year''
create table ##tmpUserDBase
,[Nominal Code] varchar(100)
insert into ##tmpUserDBase
select * from ##tmpUserDBase