Well that sure did the trick!, something new I learned :)

Thank you very much Jeff

Ewout


> Why are you using TOP 100%?  That selects all of the rows, which is the
default for SELECT, so why bother?

The LIKE operator only works on strings, so you can't use it without 
CASTing
the column to a string, but if I read you correctly, you only care about
either a specific value of CityTownID or any value at all.

The way to do this is to pass a NULL value in your parameters for a "don't
care" indicator.  I'll assume you do this for each of the three 
parameters.
Then your query would look like:

SELECT dbo.MyTable.MyID, dbo.MyTable.Name, dbo.MyTable.Street,
dbo.MyTable.CityTownID, dbo.MyTable.State
	FROM dbo.MyTable LEFT OUTER JOIN dbo.tblLocationCitiesTowns
		ON dbo.MyTable.CityTownID = 
dbo.tblLocationCitiesTowns.CityTownID
	WHERE dbo.MyTable.Cat01 = coalesce(@SicCode,dbo.MyTable.Cat01)
		AND dbo.MyTable.CityTownID = coalesce
(@CityTownID,dbo.MyTable.CityTownID)
		AND dbo.tblOOO.Name LIKE @Alpha + '%'
ORDER BY dbo.MyTable.Name;

The trick here is that coalesce() returns the first non-null value in its
argument list.  If the argument is null, then the comparison of the column
is done with itself, which of course is always true, so when the parameter
is null, the comparison is in effect a no-op - i.e. don't care.

--
Jeff Mason			Custom Apps, Inc.
Jeff@c...

-----Original Message-----
From: Evan [mailto:raisorsharp@h...]
Sent: Thursday, September 05, 2002 6:27 PM
To: sql language
Subject: [sql_language] Stored Procedure, wildcard with INT


Hello Everyone,

Well, I was writing a nice stored procedure, but then I found out that you
cannot use a wildcard '%' with an int field.

The query I was starting to write is as follows:

CREATE PROCEDURE pv_BizSICListing_CityTown
@SicCode varchar (50),
@CityTownID varchar (50),
@Alpha nvarchar (150)
AS
SELECT     TOP 100 PERCENT dbo.MyTable.MyID, dbo.MyTable.Name,
dbo.MyTable.Street, dbo.MyTable.CityTownID, dbo.MyTable.State,
FROM         dbo.MyTable LEFT OUTER JOIN
             dbo.tblLocationCitiesTowns ON dbo.MyTable.CityTownID =
dbo.tblLocationCitiesTowns.CityTownID
WHERE     (dbo.MyTable.Cat01 = @SicCode) AND (dbo.MyTable.CityTownID LIKE
@CityTownID) AND (dbo.tblOOO.Name LIKE @Alpha + '%')
ORDER BY dbo.MyTable.Name
GO


Well the problem is with dbo.tblMyTable.TownID, since the field is an INT
field.....Using asp pages I call this stored procedure and everything
goes fine as long as I give the @CityTownID a value, but there's
instances where I just want to pass a % in order to say I want to have
all records no matter WHAT CityTownID............but the
view / sp chokes on it. Anyone know how to properly script this in a
Stored Procedure?

I've just started out writing these stored Procedures, so I'm learning,
please bare with me.

Ewout

---
Change your mail options at http://p2p.wrox.com/manager.asp or
to unsubscribe send a blank email to