Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Empty parameter in SQL Query

Status
Not open for further replies.

idam

Programmer
Nov 1, 2001
5
GB
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
 

By enclosing the parameters in quotes you've prevented SQL Server from evaluating them. Change the query in the procedure as follows.

select distinct(substring(bus_name,1,1)) as letter
from member
where bus_name like '%nissan%' and

-- Note 3 consecutive single quotes in the next 2 lines
bus_category like '''%' + @search_category + '%''' and
bus_address like '''%' + @search_location + '%'''

order by letter asc
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top