Solved

Dynamic Stored Procedure and Declared Variables

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql sERVER PARSE DATA BY HOURS AND COLUMNS 2 42
PERFORMANCE OF SQL QUERY 13 65
Is there any Easy way to copy CSV to SQL Server using C# 3 69
How to query LOCK_ESCALATION 4 40
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Need to grow your business through quality cloud solutions? With everything required to build a cloud platform and solution, you may feel like the distance between you and the cloud is quite long. Help is here. Spend some time learning about the Con…

948 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

19 Experts available now in Live!

Get 1:1 Help Now