Multiple inputs into SP - 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)

Multiple inputs into SP

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-12-30 : 12:09:01

Ron writes "I am building an ASP.NET application that is meant to search a SQL table containing approx 300,000 records. There are 2 .aspx pages first one (search.aspx) needs 5 fields that the user can search on (name, city, state, zip and areacode). the user should be able to enter one or all of the search criteria. the second page (search_results.aspx) calls a stored procedure and passes those 5 variables from the previous page. the return should be speedy and contain only appropriate data (obviously). I guess my real question is, What is the best way to contruct my WHERE if any of those variables are empty. lets say someone chooses only state... how can i ignore the rest of the parameters and only return those records for that state?"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-12-30 : 12:15:34

if a parameter is not being searched on, pass a NULL value to the stored procedure.

Then, in the stored produre, you would write it like this:

create procedure SearchResults (@City varchar(20), @State varchar(20))
AS
select * from
YourData
WHERE ((@City is null) or City = @City)) AND
((@State is null) or State = @State)

you also can use LIKE; it can be more efficient sometimes I have found:

WHERE City like ISNULL(@City,'%') AND
State like ISNULL(@State,'%')

- Jeff

Krb_iiaba
Starting Member

16 Posts

Posted - 2003-12-30 : 15:00:12

Thanks for the reply... I actually did come up with a solution that works fairly well... for reference here it is

physicalAddressCity like case
when DataLength(@CITY) > 0 then '%' + @CITY + '%'
else physicalAddressCity
end

Thanks Again

X002548
Not Just a Number

15586 Posts

Posted - 2003-12-30 : 16:00:07

quote:
Originally posted by Krb_iiaba

I actually did come up with a solution that works fairly well...

I wouldn't think so....did you do a show plan on that

sssssssssssssssssssssssssssssssssccccccccccccccccccccccccccccccccaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaannnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources