Click here to Skip to main content
15,881,380 members
Please Sign up or sign in to vote.
1.00/5 (3 votes)
See more:
SQL
Create PROCEDURE  [dbo].[usp_zcCandidate_SELECT_By_Client_Status_PayRollType]       

(      

 @ClientID int = NULL,      

 @StatusID int = NULL,      

 @PayrollTypeID int = NULL ,  

 @LoggedInUserID UNIQUEIDENTIFIER      

)        

AS      

    

if (@ClientID = 0)    

set @ClientID = null    

    

if (isnull(@StatusID, -1) =-1 )    

BEGIN     

  SELECT  CandidateID, FirstName +' '+ LastName as CandidateName, DateCreate,         

    C.ClientID as ClientID, ClientName, C.StatusID, S.[Name] as Status, IsRehire        

  FROM zcCandidate C        

  LEFT JOIN zcCandidateStatus S ON C.StatusID = S.StatusID  

  INNER JOIN fn_GetAccessibleClientIDsByLoggedInUserID(@LoggedInUserID) AS AC ON C.ClientID = AC.ClientID    

  WHERE     

   (C.ClientID = ISNULL(@ClientID, C.ClientID))        

    AND ((C.StatusID = 1 OR C.StatusID = 8))    

    AND  IsPayrollee1099 & @PayrollTypeID > 0      

  ORDER BY DateCreate, LastName      

END     

 ELSE    

BEGIN    

 SELECT  CandidateID, FirstName +' '+ LastName as CandidateName, DateCreate,         

    C.ClientID as ClientID, ClientName, C.StatusID, S.[Name] as Status, IsRehire        

  FROM zcCandidate C        

  LEFT JOIN zcCandidateStatus S ON C.StatusID = S.StatusID  

  INNER JOIN fn_GetAccessibleClientIDsByLoggedInUserID(@LoggedInUserID) AS AC ON C.ClientID = AC.ClientID    

  WHERE     

   (C.ClientID = ISNULL(@ClientID, C.ClientID))        

    AND C.StatusID = @StatusID     

    AND  IsPayrollee1099 & @PayrollTypeID > 0      

  ORDER BY DateCreate, LastName     

END
Posted
Updated 7-Aug-13 18:47pm
v2
Comments
Thanks7872 8-Aug-13 0:39am    
Why?
santoshroyal 8-Aug-13 0:41am    
what why am new to this am not able to understand this
Thanks7872 8-Aug-13 0:43am    
Then we are not here to teach you something. Do some thing on your own,explain what have you tried,and if you face any problems you can post it here. Refer to code project guidelines first.
santoshroyal 8-Aug-13 0:45am    
aha
santoshroyal 8-Aug-13 0:48am    
i entered into a project so am new to these type of queries thats y am asking to explain

1 solution

It returns details from zcCandidate and zcCandidateStatus.

If the @StatusID parameter is blank, it runs the first half of the SP and returns all records with StatusID of 1 or 8...

If the @StatusID parameter not blank, it essentially runs the same query but only for a specified Status.
 
Share this answer
 
Comments
santoshroyal 8-Aug-13 0:44am    
can u please explain me step by step
_Damian S_ 8-Aug-13 1:05am    
Which part do you not understand? That was pretty much step by step.

The first part of your SP is where the parameters are. The rest is broken into two almost identical parts - the bits in the if-then-else. As I said above, the first part is used if a value is passed into the @StatusID parameter.

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