Sample Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Sample Query

Author

Topic

kamal.A
Yak Posting Veteran

70 Posts

Posted - 2008-04-09 : 09:42:21

Hai

I have one amount field in my table. I need the query to get the record where the amount field must be +/-15%.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-09 : 09:44:25

+/- 15% of what?

E 12°55'05.25"
N 56°04'39.16"

kamal.A
Yak Posting Veteran

70 Posts

Posted - 2008-04-09 : 09:49:03

For Example

Amount 1500

Amount between 1500 + 15/100 and 1500 - 15/100

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-09 : 09:51:27

SELECT Amount
FROM Table
WHERE Amount BETWEEN 1500 * 0.85 AND 1500 * 1.15

DECLARE @SearchAmount MONEY

SET @SearchAmount = 1500

SELECT Amount
FROM Table
WHERE Amount BETWEEN @SearchAmount * 0.85 AND @SearchAmount * 1.15

E 12°55'05.25"
N 56°04'39.16"

kamal.A
Yak Posting Veteran

70 Posts

Posted - 2008-04-09 : 10:04:13

Thanks.

kamal.A
Yak Posting Veteran

70 Posts

Posted - 2008-04-09 : 14:10:21

I used the above query in my existing query, it was produced the conversion error. Herewith I have inserted my query kindly go thru and give your valuable support.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 ALTER PROCEDURE  [dbo].[PropertySearch_Prospect_SearchDetails_Test]  
  
(@Postcode Varchar(50), @town Varchar(50), @village Varchar(50), @rural Varchar(50),
 @RentalType Varchar(50), @detached Varchar(50), @semi Varchar(50), @terrace Varchar(50), @farm Varchar(50),
 @NumOfBed Varchar(50), @Cat Varchar(5), @Dog Varchar(5),   
 @Other Varchar(5), @Smoking Varchar(5), @sixmnths Varchar(20), @twelemnths Varchar(20), @threesixmnths Varchar(20),
 @HoldyRentPeriod Varchar(20), @RentalCostPM Varchar(20), @Broadband  Varchar(5) )  
  
As  
  

Begin  
  
 Declare @SelectSQL As Varchar(8000), @CheckFlag as varchar(20), @SelectSQL1 As Varchar(4000),@SelectSQL2 As Varchar(4000)  
  
 Set @SelectSQL = ' '  
   
 Set @SelectSQL = @SelectSQL + 'Select PM.TenantCode, PM.PropertyId, PM.ApartmentNo, PM.FloorNumber, PM.[HouseName / No], '  
 Set @SelectSQL = @SelectSQL + 'PM.[Street Line1], PM.[Street Line2], PM.[City / Town], PM.[County / State], '  
 Set @SelectSQL = @SelectSQL + 'PM.Country, CTM.CountryName, PM.[Post / Zip code] As ''PostCode'', '  
 Set @SelectSQL = @SelectSQL + 'PL.LocationName As ''PopertyLocation'', PM.LetsType, '  
 Set @SelectSQL = @SelectSQL + 'PT.[Type] As ''PropertyType'', PM.NoBedRooms as ''NumOfBed'', PM.cat, PM.dog, '   
 Set @SelectSQL = @SelectSQL + 'PM.other, PM.Smoking, RPM.RentalPeriod, PM.[Rental cost pm], PM.Broadband '   
 Set @SelectSQL = @SelectSQL + 'From HSSPMS_Tbl_PropertyMaster PM '  
 Set @SelectSQL = @SelectSQL + 'Inner Join HSSPMS_Tbl_Country CTM On CTM.CountryId = PM.Country '  
 Set @SelectSQL = @SelectSQL + 'Inner Join HSSPMS_TblLocationDetails PL On PL.LocationCode = PM.Location '  
 Set @SelectSQL = @SelectSQL + 'Inner Join HSSPMS_TblPropertyType PT On PT.TypeCode = PM.PropertyType '  
 Set @SelectSQL = @SelectSQL + 'Inner Join HSSPMS_TblRentPeriodMaster RPM On RPM.RentPeriodCode = PM.RentalPeriod where PM.IsDeleted = ''0'' '  
             
   
 If (@PostCode <> '')   
 Begin  
  Set @SelectSQL = @SelectSQL + ' And '  
  Set @SelectSQL = @SelectSQL + ' (PM.[Post / Zip code] = ''' + @PostCode + ''') '  
 End  
   
 If (@town <> '')  
 Begin  
	If ((@village = '') And (@rural = ''))
		Begin
			Set @SelectSQL = @SelectSQL + ' And '  
			Set @SelectSQL = @SelectSQL + ' (PL.LocationCode = ''' + @town + ''') '  
		End
	Else
		Begin
			Set @SelectSQL = @SelectSQL + ' And '  
			Set @SelectSQL = @SelectSQL + ' ((PL.LocationCode = ''' + @town + ''') '  
		End
 End  

 If (@village <> '')  
 Begin  
	If ((@town = '') And (@rural = ''))  
		Begin
			Set @SelectSQL = @SelectSQL + ' And '
			Set @SelectSQL = @SelectSQL + ' (PL.LocationCode = ''' + @village + ''') '  
		End
	Else If (@town = '')
		Begin
			Set @SelectSQL = @SelectSQL + ' And '
			Set @SelectSQL = @SelectSQL + ' ((PL.LocationCode = ''' + @village + ''') '  
		End
	Else If (@rural = '')
		Begin
			Set @SelectSQL = @SelectSQL + ' Or '  
			Set @SelectSQL = @SelectSQL + ' (PL.LocationCode = ''' + @village + '''))'  			
		End
	Else
		Begin
			Set @SelectSQL = @SelectSQL + ' Or '  
			Set @SelectSQL = @SelectSQL + ' (PL.LocationCode = ''' + @village + ''')'  			
		End
 End  

 If (@rural <> '')  
 Begin  
	If ((@village = '')  And (@town = ''))
		Begin
			Set @SelectSQL = @SelectSQL + ' And '  
			Set @SelectSQL = @SelectSQL + ' (PL.LocationCode = ''' + @rural + ''') '  
		End
	Else
		Begin
			Set @SelectSQL = @SelectSQL + ' Or '  
			Set @SelectSQL = @SelectSQL + ' (PL.LocationCode = ''' + @rural + ''')) '  
		End
 End  
  
 If (@RentalType <> '')   
 Begin  
  Set @SelectSQL = @SelectSQL + ' And '  
  Set @SelectSQL = @SelectSQL + ' (PM.LetsType = ''' + @RentalType + ''' ) '  
 End  
  
 If (@detached <> '')   
 Begin  
	If ((@semi='') And (@terrace='') And (@farm =''))
		Begin
			Set @SelectSQL = @SelectSQL + ' And '  
			Set @SelectSQL = @SelectSQL + ' (PT.[TypeCode] = ''' + @detached + ''' ) '  
		End
	Else 
		Begin
			Set @SelectSQL = @SelectSQL + ' And '  
			Set @SelectSQL = @SelectSQL + ' ((PT.[TypeCode] = ''' + @detached + ''' ) '  
		End
 End   

 If (@semi <> '')   
 Begin  
	if ((@detached = '') And (@terrace='') And (@farm =''))
		Begin
			Set @SelectSQL = @SelectSQL + ' And '  
			Set @SelectSQL = @SelectSQL + ' (PT.[TypeCode] = ''' + @semi + ''' ) '  
		End
	Else if ((@terrace='') And (@farm =''))
		Begin
			Set @SelectSQL = @SelectSQL + ' Or '  
			Set @SelectSQL = @SelectSQL + ' (PT.[TypeCode] = ''' + @semi + ''' )) '  
		End
	Else if ((@detached = '') And (@farm =''))
		Begin
			Set @SelectSQL = @SelectSQL + ' And '  
			Set @SelectSQL = @SelectSQL + ' ((PT.[TypeCode] = ''' + @semi + ''' ) '  
		End
	Else if ((@detached = '') And (@terrace=''))
		Begin
			Set @SelectSQL = @SelectSQL + ' And '  
			Set @SelectSQL = @SelectSQL + ' ((PT.[TypeCode] = ''' + @semi + ''' ) '  
		End
	Else if (@terrace='') 
		Begin
			Set @SelectSQL = @SelectSQL + ' Or '  
			Set @SelectSQL = @SelectSQL + ' (PT.[TypeCode] = ''' + @semi + ''' ) '  
		End
	Else if (@detached = '')
		Begin
			Set @SelectSQL = @SelectSQL + ' And '  
			Set @SelectSQL = @SelectSQL + ' ((PT.[TypeCode] = ''' + @semi + ''' ) '  
		End
	Else if (@farm ='')
		Begin
			Set @SelectSQL = @SelectSQL + ' Or '  
			Set @SelectSQL = @SelectSQL + ' (PT.[TypeCode] = ''' + @semi + ''' ) '  
		End
	Else
		Begin
			Set @SelectSQL = @SelectSQL + ' Or '  
			Set @SelectSQL = @SelectSQL + ' (PT.[TypeCode] = ''' + @semi + ''' ) '
		End
  
 End   

 If (@terrace <> '')   
 Begin  
	if ((@detached = '') And (@semi = '') And (@farm =''))
		Begin
			Set @SelectSQL = @SelectSQL + ' And '  
			Set @SelectSQL = @SelectSQL + ' (PT.[TypeCode] = ''' + @terrace + ''' ) '  
		End
	Else if ((@semi = '') And (@farm =''))
		Begin
			Set @SelectSQL = @SelectSQL + ' Or '  
			Set @SelectSQL = @SelectSQL + ' (PT.[TypeCode] = ''' + @terrace + ''' )) '  
		End
	Else if ((@detached = '') And (@farm =''))
		Begin
			Set @SelectSQL = @SelectSQL + ' Or '  
			Set @SelectSQL = @SelectSQL + ' (PT.[TypeCode] = ''' + @terrace + ''' )) '  
		End
	Else if ((@detached = '') And (@semi = ''))
		Begin
			Set @SelectSQL = @SelectSQL + ' And '  
			Set @SelectSQL = @SelectSQL + ' ((PT.[TypeCode] = ''' + @terrace + ''' ) '  
		End
	Else if (@semi = '') 
		Begin
			Set @SelectSQL = @SelectSQL + ' Or '  
			Set @SelectSQL = @SelectSQL + ' (PT.[TypeCode] = ''' + @terrace + ''' ) '  
		End
	Else if (@detached = '')
		Begin
			Set @SelectSQL = @SelectSQL + ' Or '  
			Set @SelectSQL = @SelectSQL + ' (PT.[TypeCode] = ''' + @terrace + ''' ) '  
		End
	Else if (@farm ='')
		Begin
			Set @SelectSQL = @SelectSQL + ' Or '  
			Set @SelectSQL = @SelectSQL + ' (PT.[TypeCode] = ''' + @terrace + ''' )) '  
		End
	Else
		Begin
			Set @SelectSQL = @SelectSQL + ' Or '  
			Set @SelectSQL = @SelectSQL + ' (PT.[TypeCode] = ''' + @terrace + ''' ) '
		End
 End   
 
 If (@farm <> '')   
 Begin  
	if ((@detached = '') And (@semi = '') And (@terrace=''))
		Begin
			Set @SelectSQL = @SelectSQL + ' And '  
			Set @SelectSQL = @SelectSQL + ' (PT.[TypeCode] = ''' + @farm + ''' ) '  
		End
	Else
		Begin
			Set @SelectSQL = @SelectSQL + ' Or '  
			Set @SelectSQL = @SelectSQL + ' (PT.[TypeCode] = ''' + @farm + ''' )) '  
		End
 End   
 
 If (@NumOfBed <> '')   
 Begin  
  Set @SelectSQL = @SelectSQL + ' And '  
  if (@NumOfBed = 1)  
  Begin  
   Set @SelectSQL = @SelectSQL + ' (PM.NoBedRooms < 3 ) '  
  End  
  Else if(@NumOfBed = 2)   
  Begin   
   Set @SelectSQL = @SelectSQL + ' ((PM.NoBedRooms > 2 ) And (PM.NoBedRooms < 5 )) '   
  End  
  Else if(@NumOfBed = 3)  
  Begin   
   Set @SelectSQL = @SelectSQL + ' ((PM.NoBedRooms > 4 ) And (PM.NoBedRooms < 7 )) '   
  End  
  Else if(@NumOfBed = 4)  
  Begin   
   Set @SelectSQL = @SelectSQL + ' (PM.NoBedRooms > 6 ) '   
  End  
 End   
  
 If (@Cat <> '')   
 Begin  
  Set @SelectSQL = @SelectSQL + ' And '  
  Set @SelectSQL = @SelectSQL + ' (PM.cat = ''' + @Cat + ''' ) '   
 End   
  
 If (@Dog <> '')   
 Begin  
  Set @SelectSQL = @SelectSQL + ' And '  
  Set @SelectSQL = @SelectSQL + ' (PM.dog = ''' + @Dog + ''' ) '   
 End   
  
 If (@Other <> '')   
 Begin  
  Set @SelectSQL = @SelectSQL + ' And '  
  Set @SelectSQL = @SelectSQL + ' (PM.other = ''' + @Other + ''' ) '  
 End   
   
 If (@Smoking <> '')   
 Begin  
  Set @SelectSQL = @SelectSQL + ' And '  
  Set @SelectSQL = @SelectSQL + ' (PM.Smoking = ''' + @Smoking + ''' ) '  
 End   
  
 If (@sixmnths <> '')   
 Begin  
	If ((@twelemnths = '') And (@threesixmnths = ''))
		Begin
			Set @SelectSQL = @SelectSQL + ' And '  
			Set @SelectSQL = @SelectSQL + ' (RPM.RentPeriodCode = ''' + @sixmnths + ''' ) '  
		End
	Else
		Begin
			Set @SelectSQL = @SelectSQL + ' And '  
			Set @SelectSQL = @SelectSQL + ' ((RPM.RentPeriodCode = ''' + @sixmnths + ''' ) '  
		End
 End

 If (@twelemnths <> '')   
 Begin  
	If ((@sixmnths = '') And (@threesixmnths = '')) 
		Begin
			Set @SelectSQL = @SelectSQL + ' And '
			Set @SelectSQL = @SelectSQL + ' (RPM.RentPeriodCode = ''' + @twelemnths + ''' ) '   
		End
	Else If (@sixmnths = '')
		Begin
			Set @SelectSQL = @SelectSQL + ' And '
			Set @SelectSQL = @SelectSQL + ' ((RPM.RentPeriodCode = ''' + @twelemnths + ''' ) '   
		End
	Else If (@threesixmnths = '')
		Begin
			Set @SelectSQL = @SelectSQL + ' Or '  
			Set @SelectSQL = @SelectSQL + ' (RPM.RentPeriodCode = ''' + @twelemnths + ''' )) '   
		End
	Else
		Begin
			Set @SelectSQL = @SelectSQL + ' Or '  
			Set @SelectSQL = @SelectSQL + ' (RPM.RentPeriodCode = ''' + @twelemnths + ''' ) '   
		End
 End  
  
 If (@threesixmnths <> '')  
 Begin  
	If ((@sixmnths = '')  And (@twelemnths = ''))
		Begin
			Set @SelectSQL = @SelectSQL + ' And '  
			Set @SelectSQL = @SelectSQL + ' (RPM.RentPeriodCode = ''' + @threesixmnths + ''' ) ' 
		End
	Else
		Begin
			Set @SelectSQL = @SelectSQL + ' Or '  
			Set @SelectSQL = @SelectSQL + ' (RPM.RentPeriodCode = ''' + @threesixmnths + ''' )) ' 
		End
 End  

 If (@HoldyRentPeriod <> '')   
 Begin  
  Set @SelectSQL = @SelectSQL + ' And '  
  Set @SelectSQL = @SelectSQL + ' (RPM.RentPeriodCode = ''' + @HoldyRentPeriod + ''' ) '  
 End   
  
 If (@RentalCostPM <> '')   
 Begin  
  Set @SelectSQL = @SelectSQL + ' And '  
  Set @SelectSQL = @SelectSQL + ' (PM.[Rental cost pm] BETWEEN ''' + @RentalCostPM *0.85  +''' AND ''' + @RentalCostPM *1.15  +''') '  
 End   
  
 If (@Broadband <> '')   
 Begin  
  Set @SelectSQL = @SelectSQL + ' And '  
  Set @SelectSQL = @SelectSQL + ' (PM.Broadband = ''' + @Broadband + ''' ) '  
 End    
  
Print @SelectSql  
Exec(@SelectSql)  
  
   
  
 if EXISTS (select * from INFORMATION_SCHEMA.tables where table_name = 'SearchResult')  
  Begin  
   Drop Table SearchResult     
  End  
  
   
 Set @SelectSql1 = 'Select * Into SearchResult From ( ' + @SelectSQL + ' )a'  
 Exec(@SelectSql1)  
  
   
End

I highlighted the code.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-09 : 14:19:11

change like this and try

Set @SelectSQL = @SelectSQL + ' (PM.[Rental cost pm] BETWEEN ' + @RentalCostPM *0.85 +' AND ' + @RentalCostPM *1.15 +') '

kamal.A
Yak Posting Veteran

70 Posts

Posted - 2008-04-09 : 14:21:59

Again same error "Arithmetic overflow error converting varchar to data type numeric." Kindly help me.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-09 : 14:39:27

Thats because you have some bad numeric data in your varchar column.Whats the data type of Rental cost pm?

kamal.A
Yak Posting Veteran

70 Posts

Posted - 2008-04-09 : 14:48:20

Decimal(6,2) in the table.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-09 : 14:53:21

Set @SelectSQL = @SelectSQL + ' (PM.[Rental cost pm] BETWEEN ' + CAST(@RentalCostPM *0.85 AS VARCHAR(20)) +' AND ' + CAST(@RentalCostPM *1.15 AS VARCHAR(20)) +') '

E 12°55'05.25"
N 56°04'39.16"

kamal.A
Yak Posting Veteran

70 Posts

Posted - 2008-04-09 : 14:55:57

I already try the above method, but the still same error occurred. Kindly help me.

cvipin
Yak Posting Veteran

51 Posts

Posted - 2008-04-09 : 23:40:36

Try this..

SET @RentalCostPM = LTRIM(RTRIM(@RentalCostPM))

Set @SelectSQL = @SelectSQL + ' (PM.[Rental cost pm] BETWEEN ' + CONVERT(decimal(18,2), @RentalCostPM) *0.85 +' AND ' + CONVERT(decimal(18,2), @RentalCostPM) *1.15 +') '

Change decimal(18,2) to the required length as per your requirement. Also what is the data type of PM.[Rental cost pm]? If the data type is varchar, convert to decimal and then compare. Let me know if this works.

Thanks
Vipin

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 01:06:03

Post the EXACT error message you get when using the line I posted 04/09/2008 : 14:53:21.
Maybe it is not that line anymore that is wrong? I think you have an error in some other line.

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-10 : 04:45:09

Also when you dont pass object names as parameters. you dont need dynamic sql
www.sommarskog.se/dyn-search.html

Madhivanan

Failing to plan is Planning to fail

kamal.A
Yak Posting Veteran

70 Posts

Posted - 2008-04-10 : 13:48:59

The exact error message is

Msg 8114, Level 16, State 5, Procedure PropertySearch_Prospect_SearchDetails_Test, Line 315
Error converting data type varchar to numeric.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources