Solved

Dynamic Stored Procedure and Declared Variables

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

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…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

770 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