[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

URGENT: Stored Procedure Dynamic WHERE Clause

Posted on 2005-05-17
6
Medium Priority
?
249 Views
Last Modified: 2010-03-19
Please Help i am trying to build a Stored Procedure in SQL 2000 which dynamically builds the WHERE Clause depending on the Inputed Variables.

CREATE PROCEDURE dbo.W_S_search_propertycard
(
      @Code       varchar(25),
      @MinPrice      varchar(7)
)
AS
if LEN(RTRIM(@Code)) = 0 SET @code = NULL
IF @MinPrice = '£ Min' SET @MinPrice = NULL

DECLARE @SQL1 varchar(255)
DECLARE @SQL2 varchar(255)

SET @SQL1 =      'SELECT     dbo.Property.PropertyID, dbo.Property.RegisteredUserID, dbo.Property.Code, dbo.Property.Town, dbo.County.Name, dbo.Property.Bedrooms,  dbo.Property.Price, dbo.Property.Image1URL, dbo.Property.Summary, dbo.Property.TourURL'
SET @SQL2 =      ' FROM         dbo.Property INNER JOIN  dbo.County ON dbo.Property.CountyID = dbo.County.CountyID '
IF @Code <> NULL
      BEGIN
            DECLARE @SQL3 varchar (255)
            SET @SQL3 = ' WHERE dbo.Property.Code = ' + @Code
            EXECUTE (@SQL1 + @SQL2 + @SQL3)
      END
ELSE
      BEGIN
            EXECUTE (@SQL1+ @SQL2)
      END
GO


When i run this StoredProcedure i get the following error, which is rubish as dbo.property.code Column is of DataType varchar(25)

Thanks in advance
0
Comment
Question by:Robinsonx6
  • 3
  • 2
6 Comments
 
LVL 2

Author Comment

by:Robinsonx6
ID: 14019817
Sorry the error i get is 'Syntax error converting the varchar value '111000-111009' to a column of data type int.'
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 14020072
That *is* very odd if column "Code" is of datatype VARCHAR.  The comparison should work fine.  

Btw, as long as '111000-111009' is a single value.  If it is intended to be a range specification of '111000' thru '111009', then naturally some code needs added to parse the variable into two separate variables and add a BETWEEN to the WHERE.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 14020077
Btw, you don't have to use dynamic SQL for this although it might perform better that way if you don't have an index on column "Code".
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 2

Author Comment

by:Robinsonx6
ID: 14020177
the value 111000-111009 is actually just a string value and is the identity code for the record not a range, i need to dynamic build as there are about 10 clauses which might or might not be used in the WHERE depending if they have a value or not
0
 
LVL 23

Accepted Solution

by:
adathelad earned 2000 total points
ID: 14020223
Hi,

I think it's because you need to concatenate extra single quotes around @Code as once it's added into the string, it will probably treat 111000-1110009 as a mathematical calculation, before applying it to the "=" where clause statement, hence the error.

Try replacing this:
IF @Code <> NULL
     BEGIN
          DECLARE @SQL3 varchar (255)
          SET @SQL3 = ' WHERE dbo.Property.Code = ' + @Code
          EXECUTE (@SQL1 + @SQL2 + @SQL3)
     END


with this:
IF @Code <> NULL
     BEGIN
          DECLARE @SQL3 varchar (255)
          SET @SQL3 = ' WHERE dbo.Property.Code = ' + '''' + @Code + ''''
          EXECUTE (@SQL1 + @SQL2 + @SQL3)
     END
0
 
LVL 2

Author Comment

by:Robinsonx6
ID: 14020712
Thanks adathelad, i was thinking along the same lines but i didnt put enough single quotes in :-)
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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

834 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