use parameters in where clause with in a sql string

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.
Thanks
if OBJECT_ID('tempdb..##tmpUserDBase') IS NOT NULL
	begin 
	   drop 
	   table       ##tmpUserDBase
	end


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
	(DBASE                  varchar(100)
	 ,[Nominal Code]	varchar(100)
         ,[Date]		date
	 ,[Period]		varchar(100)
         ,[Year]		varchar(1)
	 ,[Narrative]		varchar(250)
	 ,[D/C]			float)
				  
	insert into ##tmpUserDBase
	exec (@STR)
	
	
	select * from ##tmpUserDBase

Open in new window

deanmachine333Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

wdosanjosCommented:
Please try the following:

if OBJECT_ID('tempdb..##tmpUserDBase') IS NOT NULL
	begin 
	   drop 
	   table       ##tmpUserDBase
	end


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
	(DBASE                  varchar(100)
	 ,[Nominal Code]	varchar(100)
         ,[Date]		date
	 ,[Period]		varchar(100)
         ,[Year]		varchar(1)
	 ,[Narrative]		varchar(250)
	 ,[D/C]			float)
				  
	insert into ##tmpUserDBase
	exec (@STR)
	
	
	select * from ##tmpUserDBase

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
knightEknightCommented:
You may need to concatinate the values of the params instead of their names:


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 + '''
            '
0
deanmachine333Author Commented:
Thanks guys , i did try that before but didnt do it on the @year , oh and giving points to both but most point were given to wdosanjos as he posted it first.

thanks again.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.