Combine data bases on parameters
Posted on 2009-05-18
I have this sp below...if one of the fields is not null, all data is returned based on that parameter. If 2 parameters are not null, then only data fitting those non null parameters should eb returned and if 3 parameters, etc...as above with 2. What I'm getting is data for only one parameter even when I input 2.
If then statement?
ALTER PROCEDURE [dbo].[usp_getPOC1]
@UserID int = NULL,
@CenterID int = 1,
@ProjectID int = 1
SELECT c.Center_Desc, p.Project, u.UserID, r.Last_Name, r.First_Name, r.Office_Phone, u.User_Email from dbo.tbl_User u
Join dbo.tbl_Registration r on u.UserID = r.UserID, dbo.LU_Center_Site c, dbo.LU_Project p
@UserID is not null and @UserID = u.UserID and u.POC = '1' or
@CenterID is not NULL and @CenterID = c.CenterID or
@ProjectID is not null and @ProjectID = p.ProjectID
ORDER BY r.Last_Name