• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 312
  • Last Modified:

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

0
deanmachine333
Asked:
deanmachine333
2 Solutions
 
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
 
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now