Solved

Combine data bases on parameters

Posted on 2009-05-18
2
218 Views
Last Modified: 2012-05-07
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?

Thx

ALTER PROCEDURE [dbo].[usp_getPOC1]

@UserID int = NULL,
@CenterID int = 1,
@ProjectID int = 1

AS

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
Where
@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
0
Comment
Question by:Glen_D
2 Comments
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 500 total points
ID: 24413150
try this:


ALTER PROCEDURE [dbo].[usp_getPOC1]
 
@UserID int = NULL,
@CenterID int = 1,
@ProjectID int = 1
 
AS
 
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
Where u.POC = '1' AND --this always requires u.POC to = '1' -- which I think is what you mean.
(@UserID is null OR @UserID = u.UserID) and 
( @CenterID is NULL OR @CenterID = c.CenterID) AND
(@ProjectID is null OR @ProjectID = p.ProjectID)
ORDER BY r.Last_Name

Open in new window

0
 

Author Closing Comment

by:Glen_D
ID: 31582614
Perfect...thx Much  Glen
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

791 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question