create proc [search]
    @units int
as
select
    *
from
    table
where
    id is not null and
    units >= coalesce(@units,units) + 1

where
    id is not null and
    ((@units <> 0 and units > @units) or
    (isnull(@units,0) = 0))

quote:

---

Sorry it has to be a dynamic sql. Is there any special encoding i could do ?

---

quote:

---

WHY?

Brett

8-)

---

quote:

---

Because i had a lot of different data coming into stored procedure and i only have problems with integer because of the quotes

---

quote:

---

OK, Try the following:

But first:

1. You definetly cut and pasted this out of Access. Access makes messy code, I wouldn't do that if I was you
2. Change the values you are passing from the word empty and just allows Nulls
3. Are you building the string because the query is a dog?
4. Label your tables with something simple, so you only have to qualify the columns with 1 or 2 letters (t1.columnname)
5. Don't use spaces in the names of Objects, special chars either (although I reserve the right to defend "_" from my DB2 Days)
6. I'm not going to begin to even try and understand what's going on here. You should really simply what you're doing.
7. Never use SELECT * in a sproc

Anyway, GOOD LUCK, and hope this helps.


CREATE PROCEDURE [PROPERTIES SEARCH]
@ArborUserID varchar(50) = Null,
@PropertyAddress varchar(300) = Null,
@PropertyCity varchar(100) = Null,
@PropertyState char(10) = Null,
@PropertyZipCode char(50) = Null,
@NumberOfUnits char(50) = Null,
@NumberOfFloors char(50) = Null,
@ClosingDateFrom varchar(100) = Null,
@ClosingDateTo varchar(100) = Null,
@MinimumPrice char(50) = Null,
@MaximumPrice char(50) = Null,
@Properties varchar(100) = Null,
@Sort varchar(100)
AS

SELECT PROPERTY.*, CONTACT.SALES_ID AS B_SALES_ID,"
S_CONTACT.SALES_ID AS S_SALES_ID, SB_CONTACT.SALES_ID AS SB_SALES_ID,
LB_CONTACT.SALES_ID AS LB_SALES_ID, USERINFO.FULLNAME AS B_FULLNAME,
USERINFO1.FULLNAME AS LB_FULLNAME, USERINFO2.FULLNAME AS S_FULLNAME,
USERINFO3.FULLNAME AS SB_FULLNAME
FROM USERINFO
INNER JOIN CONTACT ON USERINFO.USERID = CONTACT.SALES_ID
RIGHT OUTER JOIN USERINFO USERINFO2
INNER JOIN CONTACT S_CONTACT ON USERINFO2.USERID = S_CONTACT.SALES_ID
RIGHT OUTER JOIN CONTACT SB_CONTACT
INNER JOIN USERINFO USERINFO3 ON SB_CONTACT.SALES_ID = USERINFO3.USERID
RIGHT OUTER JOIN PROPERTY ON SB_CONTACT.CONTACT_ID = PROPERTY.[Selling Broker]
ON S_CONTACT.CONTACT_ID = PROPERTY.Seller ON CONTACT.CONTACT_ID = PROPERTY.Buyer
LEFT OUTER JOIN USERINFO USERINFO1
INNER JOIN CONTACT LB_CONTACT ON USERINFO1.USERID = LB_CONTACT.SALES_ID
ON PROPERTY.[Listing Broker] = LB_CONTACT.CONTACT_ID
WHERE (PROPERTY.PROPERTY_ID IS NOT NULL)
AND PROPERTY.[PROPERTY ADDRESS] = @PropertyAddress
AND PROPERTY.[PROPERTY CITY] Like '%' + @PropertyCity + '%'
AND PROPERTY.[PROPERTY STATE] = ltrim(rtrim(@PropertyState))
AND PROPERTY.[PROPERTY ZIP CODE] = ltrim(rtrim(@PropertyZipCode))
AND PROPERTY.[NUMBER OF UNITS] >= ltrim(rtrim(@NumberOfUnits))
AND PROPERTY.[NUMBER OF FLOORS] >= ltrim(rtrim(@NumberOfFloors))
AND PROPERTY.[CLOSING DATE] BETWEEN ltrim(rtrim(@ClosingDateFrom)) AND ltrim(rtrim(@ClosingDateTo))
AND PROPERTY.[SALES PRICE] BETWEEN + ltrim(rtrim(@MinimumPrice)) AND ltrim(rtrim(@MaximumPrice))
AND (CONTACT.SALES_ID = @ArborUserID
OR LB_CONTACT.SALES_ID = @ArborUserID
OR S_CONTACT.SALES_ID = @ArborUserID
OR SB_CONTACT.SALES_ID = @ArborUserID)
ORDER BY CASE WHEN @SORT = 'S_SALES_ID' THEN S_CONTACT.SALES_ID
WHEN @SORT = 'SB_SALES_ID' THEN SB_CONTACT.SALES_ID
END
-- ADD A WHEN CLAUSE FOR EVERY VALUE YOU CAN HAVE FOR @SORT

Brett

8-)

Edited by - x002548 on 03/13/2003 16:08:04

---

quote:

---

Ok, Try wrapping the local variables with this:

IsNull(@LocVar,'')

Give that a try, and let me know.



Brett

8-)

---