Solved

use parameters in where clause with in a sql string

Posted on 2011-09-07
3
284 Views
Last Modified: 2012-05-12
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
Comment
Question by:deanmachine333
3 Comments
 
LVL 23

Accepted Solution

by:
wdosanjos earned 400 total points
ID: 36496096
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
 
LVL 33

Assisted Solution

by:knightEknight
knightEknight earned 100 total points
ID: 36496118
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
 

Author Closing Comment

by:deanmachine333
ID: 36496202
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question