Click here to Skip to main content
15,029,596 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
)
as
if(@BusinessName  is not null  and @BusinessDescription is null)
begin
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 + '%')  
end


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?
Posted
Updated 13-Dec-10 18:18pm
v2
Comments
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)
  or
  (BusinessDescription Like  @BusinessDescription + '%' and @BusinessDescription is not null)
)

And you dont need the if(@BusinessName is not null and @BusinessDescription is null) anymore
   
Comments
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