Wildcard for numerical fields - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Wildcard for numerical fields

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-10-22 : 22:46:38

Steve Hill writes "I have a table with several columns, int, smallint, tinyint, date, etc. I want to use a stored procedure which returns rows having criteria such as :

c1=5, c2=don't care (inc nulls), c3=77

It's the "don't care" bit that's stumped me. I've trawled the help and several forums but I can't find a wildcard for numerical or date fields like the "LIKE %" stuff for strings.

Best I've come up with is an algorithm that uses the

BETWEEN @x AND @y

syntax where @x=@y for specific values and don't care sets @x=min allowed and @y=max allowed for the particular field type in question. This totally ingores NULLs though.

For example -

CREATE PROCEDURE giveitup(@match int) AS

DECLARE @x=int,@y=int

IF @match IS NULL
BEGIN
SET @x=-32768
SET @y=32767
END
ELSE
BEGIN
SET @x=@match
SET @y=@match
END

SELECT * FROM table1
WHERE c1 BETWEEN @x AND @y

This seems really clumsy and doesn't take account of nulls. I need to extend it to all other columns in the table with AND in the WHERE clause also, but this should be trivial once the above is made to work.

I'm running Win2k and SQL Server 2k.

Can you help ?

Steve"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources