CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2008
Aliasgar Rajpiplawala
Aliasgar Rajpiplawala

Reputation: 656

Use If between the where clause

I don't know how exactly I should frame my Question as even I don't know what exactly I have to search. May be this is the best place to put my question. I want to use If condition in where clause this can be done using case but my problem is I don't want to use it after a column name instead I want to avoid entire execution of that column. You can understand it by going through my procedure in the last I have commented, So I want to achieve something like that as I want to write the same procedure again for that condition which i think can be achievable by doing something like that. I searched on internet what I got is to use case in where which doesn't satisfy my purpose. Please look the last lines to understand the problem. Thanx

    USE [overseascrm]
GO
/****** Object:  StoredProcedure [dbo].[candidatesearch]    Script Date: 05-07-2014 00:48:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[candidatesearch] 
@candidate_id varchar(50),
@firstname varchar(50),
@lastname varchar(50),
@emailid_1 varchar(100),
@mobile_1 varchar(20),
@from varchar(50),
@to varchar(50),
@agentid varchar(50),
@passportno varchar(50),
@profession int,
@isactive bit
as

begin
DECLARE @candidateid_var  varchar(50)
DECLARE @firstname_var varchar(50)
DECLARE @lastname_var varchar(50)
DECLARE @emailid1_var varchar(100)
DECLARE @mobile_1_var varchar(20)
DECLARE @agentid_var varchar(50)


IF(@agentid = '')
begin
SET @agentid_var = '%'
END ELSE BEGIN
SET @agentid_var = @agentid
END


IF (@candidate_id = '') BEGIN
SET @candidateid_var = '%'
END ELSE BEGIN
SET @candidateid_var = @candidate_id
END

IF (@firstname = '') BEGIN
SET @firstname_var = '%'
END ELSE BEGIN
SET @firstname_var = @firstname
END

IF (@lastname = '') BEGIN
SET @lastname_var = '%'
END ELSE BEGIN
SET @lastname_var = @lastname
END

IF (@emailid_1 = '') BEGIN
SET @emailid1_var = '%'
END ELSE BEGIN
SET @emailid1_var = @emailid_1
END

IF (@mobile_1 = '') BEGIN
SET @mobile_1_var = '%'
END ELSE BEGIN
SET @mobile_1_var = @mobile_1
END

IF (@from = '') BEGIN
SELECT
    *
FROM candidate C
LEFT JOIN candidate_profession_map CM
    ON C.candidate_id = CM.candidate_id
LEFT JOIN passport_details PD
    ON C.candidate_id = PD.candidate_id
WHERE C.candidate_id LIKE '' + @candidateid_var + '%'
AND firstname LIKE '' + @firstname_var + '%'
AND lastname LIKE '' + @lastname_var + '%'
AND emailid_1 LIKE '' + @emailid1_var + '%'
AND mobile_1 LIKE '' + @mobile_1_var + '%'
AND agent_id LIKE '' + @agentid_var + '%'
AND CM.profession_id = @profession
AND C.isactive = @isactive
AND PD.passport_no = @passportno
END ELSE BEGIN
SELECT
    *
FROM candidate C
LEFT JOIN candidate_profession_map CM
    ON C.candidate_id = CM.candidate_id
LEFT JOIN passport_details PD
    ON C.candidate_id = PD.candidate_id
WHERE C.candidate_id LIKE '' + @candidateid_var + '%'
AND firstname LIKE '' + @firstname_var + '%'
AND lastname LIKE '' + @lastname_var + '%'
AND emailid_1 LIKE '' + @emailid1_var + '%'
AND mobile_1 LIKE '' + @mobile_1_var + '%'
AND agent_id LIKE '' + @agentid_var + '%'
AND C.addedon BETWEEN CONVERT(DATETIME, @from, 103) AND CONVERT(DATETIME, @to, 103)
--IF (@profession <> 0)BEGIN
--AND CM.profession_id = @profession
--END
AND C.isactive = @isactive
OR PD.passport_no = @passportno
END

END

Upvotes: 0

Views: 71

Answers (2)

mehdi lotfi
mehdi lotfi

Reputation: 11591

ALTER procedure [dbo].[candidatesearch] 
    @candidate_id varchar(50),
    @firstname varchar(50),
    @lastname varchar(50),
    @emailid_1 varchar(100),
    @mobile_1 varchar(20),
    @from varchar(50),
    @to varchar(50),
    @agentid varchar(50),
    @passportno varchar(50),
    @profession int,
    @isactive bit
AS Begin

    SELECT *
    FROM candidate C
    LEFT JOIN candidate_profession_map CM ON C.candidate_id = CM.candidate_id
    LEFT JOIN passport_details PD ON C.candidate_id = PD.candidate_id
    WHERE (@candidateid='' or C.candidate_id LIKE @candidateid + '%')
    AND (@firstname = '' or firstname LIKE @firstname + '%')
    AND (@lastname = '' or lastname LIKE @lastname + '%')
    AND (@emailid_1='' or emailid_1 LIKE @emailid1 + '%')
    AND (@mobile_1 = '' or mobile_1 LIKE @mobile_1 + '%')
    AND (@agentid='' agent_id LIKE @agentid + '%')
    AND C.isactive = @isactive
    And ((@from='' 
               AND CM.profession_id = @profession 
               AND PD.passport_no = @passportno) 
         or (@from<>'' 
               And C.addedon BETWEEN CONVERT(DATETIME, @from, 103) AND CONVERT(DATETIME, @to, 103) 
               OR PD.passport_no = @passportno)

End

Upvotes: 0

user3805499
user3805499

Reputation: 21

You could use dynamic sql execution where you create your select statement as varchar to your liking, executing it at the end using sp_executesql

http://msdn.microsoft.com/en-us/library/ms188001.aspx

Upvotes: 1

Related Questions