?
Solved

Dynamic Stored Procedure and Declared Variables

Posted on 2008-10-30
1
Medium Priority
?
136 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 2000 total points
ID: 22846548
 + "AND Ads_AskingPrice BETWEEN " +convert(nvarchar(20), @PriceFrom )+ " AND " +
convert(nvarchar(20), @PriceTo) + " "
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
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…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

762 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