Hi,
I have a simple stored procedure which expects three parameters. However I want to return results regardless of whether all three parameters are supplied. This is a simple search engine, which if only one parameter is supplied then the other two will be set to wild card values.
When I run the stored procedure I have it fails to return the correct values ( 0 records ). I am presuming SQL Server doesn't replace the empty parameter value with a wildcard value in the sp "like" statement. Can anyone help me out with this?
sp_WWW
======
select distinct(substring(bus_name,1,1)) as letter from member
where bus_name like '%nissan%' and
bus_category like '%@search_category%' and
bus_address like '%@search_location%'
order by letter asc
exec sp_
fails to return anything. But if I use
select distinct(substring(bus_name,1,1)) as letter from member
where bus_name like '%nissan%' and
bus_category like '%%' and
bus_address like '%%'
order by letter asc
I get the expected 3 records returned.
TIA, Ida
I have a simple stored procedure which expects three parameters. However I want to return results regardless of whether all three parameters are supplied. This is a simple search engine, which if only one parameter is supplied then the other two will be set to wild card values.
When I run the stored procedure I have it fails to return the correct values ( 0 records ). I am presuming SQL Server doesn't replace the empty parameter value with a wildcard value in the sp "like" statement. Can anyone help me out with this?
sp_WWW
======
select distinct(substring(bus_name,1,1)) as letter from member
where bus_name like '%nissan%' and
bus_category like '%@search_category%' and
bus_address like '%@search_location%'
order by letter asc
exec sp_
fails to return anything. But if I use
select distinct(substring(bus_name,1,1)) as letter from member
where bus_name like '%nissan%' and
bus_category like '%%' and
bus_address like '%%'
order by letter asc
I get the expected 3 records returned.
TIA, Ida