Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 138
  • Last Modified:

Dynamic Stored Procedure and Declared Variables

Firstly, thanks for your help.

I get this error:

Arithmetic overflow error converting varchar to data type numeric. on line 27. It occurs on this line:

             + "AND Ads_AskingPrice BETWEEN " + @PriceFrom + " AND " + @PriceTo + " "

I don't know how to convert to something that can be used.

Thanks!
Nick
CREATE PROCEDURE [dbo].[SP_SearchAds]
	@Areas			nvarchar(200)		= "",
	@Counties		nvarchar(200)		= "",
	@Categories		nvarchar(200)		= "",
	@PriceFrom		decimal			= 0,
	@PriceTo		decimal			= 0,
	@Keyword		nvarchar(200)		= "",
	@Relocatable		bit			= 0,
	@HomeBased		bit			= 0
 
AS
	BEGIN
		DECLARE @SQLStatement nvarchar(2000)
		CREATE TABLE #TempTable
		(
			Brokers_AccountType int, 
			Ads_Id int, Ads_UserId uniqueidentifier, 
			Ads_Headline nvarchar(500), 
			Ads_ProvincesId int, 
			Ads_City nvarchar(200), 
			Ads_AskingPrice decimal,
			Ads_AnnualRevenue decimal, 
			Ads_ReasonSelling text,
			Ads_GeneralDescription text
		)
 
		SET @SQLStatement = "SELECT Brokers_AccountType,  Ads_Id, Ads_UserId, Ads_Headline, Ads_ProvincesId, Ads_City, Ads_AskingPrice, Ads_AnnualRevenue, Ads_ReasonSelling, Ads_GeneralDescription "
		 + "FROM Ads "
		 + "LEFT JOIN Brokers ON Ads_UserId = Brokers_UserId "
		 + "WHERE Ads_Active = 1 "
		 + "AND Ads_AskingPrice BETWEEN " + @PriceFrom + " AND " + @PriceTo + " "
 
		IF NOT @Counties =  '' 
		 + "AND Ads_CountiesId IN (" + @Counties + ") "
 
		IF NOT @Categories =  ''
		+ "AND Ads_Category1Id IN (" + @Categories + ") "
		IF NOT @Categories =  ''
		+ "AND Ads_Category2Id IN (" + @Categories + ") "
 
		IF NOT @Keyword = ''
		 + "AND Ads_Headline LIKE '%" + @Keyword + "%' "
 
		 + "AND Ads_Relocatable  =   0 "
 
		 + "AND Ads_HomeBased = 0 "
 
		IF NOT @Areas = ''
		 + "AND Ads_ProvincesId IN (" + @Areas + ") "
 
		 + "ORDER BY Brokers_AccountType DESC, Ads_InsertDate ASC "
		
		INSERT #TempTable
		EXEC (@SQLStatement)
		
		SELECT *, @SQLStatement AS statement FROM #TempTable
		DROP TABLE #TempTable
	END
GO

Open in new window

0
internetman
Asked:
internetman
1 Solution
 
sm394Commented:
 + "AND Ads_AskingPrice BETWEEN " +convert(nvarchar(20), @PriceFrom )+ " AND " +
convert(nvarchar(20), @PriceTo) + " "
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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