Click here to Skip to main content
14,269,076 members
Rate this:
Please Sign up or sign in to vote.
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

Rate this:
Please Sign up or sign in to vote.

Solution 1

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, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100