Click here to Skip to main content
15,743,429 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I am making a procedure for usersearch that has the following parameter:
@BusinessName nvarchar(200) = null,
@BusinessDescription nvarchar(250) =null

create procedure [dbo].[UserSearch]
@BusinessName nvarchar(200) = null,
@BusinessDescription nvarchar(250) =null
if(@BusinessName  is not null  and @BusinessDescription is null)
select BusinessName,BusinessLikes,BusinessUrl,BusinessAddress2,CountryName, UserId, BusinessCity,BusinessAddress1,StateName,BusinessCreationDate,BusinessTotalReviews,BusinessRatingValue,BusinessKeywords,
            case when BusinessLogo is null or BusinessLogo = '' then 'default.gif' 
            else BusinessLogo end as BusinessLogo,BusinessCategories,substring(BusinessBriefDescription,1,60) as BusinessBriefDescription,BusinessId,BusinessPostalCode
            from  tblBusinesses where BusinessStatus =1 and  (BusinessName Like  @BusinessName + '%')  

Now I need:
(if @businessdescription is not null and @businessname is null)

The problem is that I can use this condition but for this I have to use again the avobe select query. I only need to change in where condition. Can I use my next condition in where clause? If yes than how?
Updated 13-Dec-10 18:18pm
JF2015 14-Dec-10 0:18am    
Edited to add code formatting.

1 solution

Perhaps the following condition would help
where BusinessStatus =1 and
  (BusinessName Like  @BusinessName + '%' and @BusinessName  is not null)
  (BusinessDescription Like  @BusinessDescription + '%' and @BusinessDescription is not null)

And you dont need the if(@BusinessName is not null and @BusinessDescription is null) anymore
Share this answer
balongi 14-Dec-10 0:51am    
But if both parameter are not null then it never reach to second parameter because of ('or') condition. it accept first and give result basis of business name , however i put wrong description

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900