Solved

Dynamic Stored Procedure and Declared Variables

Posted on 2008-10-30
1
130 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
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

Backup Your Microsoft Windows Server®

Backup 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.

Join & Write a Comment

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

705 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now