troubleshooting Question

use parameters in where clause with in a sql string

Avatar of deanmachine333
deanmachine333Flag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft SQL ServerMicrosoft SQL Server 2008SSRS
3 Comments2 Solutions332 ViewsLast Modified:
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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 3 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros