Solved

Dynamic Stored Procedure and Declared Variables

Posted on 2008-10-30
1
134 Views
Last Modified: 2013-11-05
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
Comment
Question by:internetman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 13

Accepted Solution

by:
sm394 earned 500 total points
ID: 22846548
 + "AND Ads_AskingPrice BETWEEN " +convert(nvarchar(20), @PriceFrom )+ " AND " +
convert(nvarchar(20), @PriceTo) + " "
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

733 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