Solved

Dynamic Stored Procedure and Declared Variables

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

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…
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…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

726 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