SQL Query Help

[Bell1991]

This could be somewhat of a programming question (i hope not).. I am writting a stored procedure and i have 4 values being passed into it.. The values could be null - which i check for.. I need to write a query that select * from tableName where column = column..

For example:

Name = 'Test'
Status = 'Active'
State = ""
City = ''

Now i need to find out what is null and not null - then build my query to add in the where clause of the values that were passed in that were not null..

Does this make senes?

So in the example above:
select * from tableName where name = 'test' and status='active'

Thanks

 

[rrrkrishnan]

Something lke this should work.

where_clause=
(CASE WHEN &name IS NOT NULL THEN 'and name =' || &name else ' ' END) ||
(CASE WHEN &status IS NOT NULL THEN 'and status=' || &status else ' ' END) ||
(CASE WHEN &state IS NOT NULL THEN 'and state =' || &state else ' ' END) ||
(CASE WHEN &city IS NOT NULL THEN 'and city =' || &city else ' ' END) ,1,3)

sql_stmt = 'select * from tablename' || CASE WHEN SUBSTR(where_clause,1,3)='and' THEN 'where ' || SUBSTR(where_clause,4)
else ' ') END

 

[rac2]

Hari, hari,

Sometimes you have to build strings as you show. In this situation there is a single query that always works.

SELECT * FROM AddressLabels
WHERE
    ( @name   IS NULL OR name   = @name )
AND ( @status IS NULL OR status = @status )
AND ( @state  IS NULL OR state  = @state )
AND ( @city   IS NULL OR city   = @city)
[/codes]
When a parameter is null every row mathches the condition.
When a parameter is not null only rows with that value match.

I would not assume that a parameter with an empty value is null.  I think this might depend on the application language, the database connection component, and the database.  But you can work out the details for your situation.