Filtering a three table join with optional para

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Filtering a three table join with optional para

Author

Topic

BendJoe
Posting Yak Master

128 Posts

Posted - 2007-12-21 : 14:03:27

Hi.
I am working on a procedure which can filter data through three tables.
First Table is User
---------------------------
UserName UserID
---------------------------

Second Table is Customer
---------------------------
UserID Cust_Site_Int_ID
---------------------------

Third Table is l Details
---------------------------------------------------------------
Cust_Site_Int_ID Created_Date Status
----------------------------------------------------------------

I want to create a stored procedure which can filter through these tables. I will have four input fields in my application to accept parameters. The four parameters will be UserName, two date fields which specifies a range of dates and status. I want the filter to work like this, if only one parameter is provided it will use only that parameter if additional parameter is given it will use the AND logic.

I tried to do a Proc but it is not working any way here it is. So you will get a better idea of what I am trying to achieve.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
ALTER PROCEDURE [dbo].[FilterServiceTickets]
-- Add the parameters for the stored procedure here
@userName varchar(20)= NULL,
@startDate datetime =NULL ,
@endDate datetime = NULL,
@status int = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT SERVICE_TICKET.SERVICE_TICKET_CODE, SERVICE_TICKET.PROBLEM_REPORTED, SERVICE_TICKET.CONTACT_NUMBER,
SERVICE_TICKET.STATUS
FROM aspnet_Users INNER JOIN
BuildingAddress ON aspnet_Users.UserId = BuildingAddress.UserID INNER JOIN
SERVICE_TICKET ON BuildingAddress.Customer_Site_Int_ID = SERVICE_TICKET.CUST_SITE_INT_ID
WHERE ((aspnet_Users.UserName = @userName)OR (@userName=NULL))
AND ((SERVICE_TICKET.STATUS = @status) OR (status=NULL))
AND ((SERVICE_TICKET.CREATED_DATE >=@startDate AND SERVICE_TICKET.CREATED_DATE <=@endDate ) OR (@startDate=NULL)OR (@endDate=NULL)OR(@startDate=NULL AND @endDate=NULL))
END

Could some one please help me with this.
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-21 : 14:07:12

[code]set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
ALTER PROCEDURE [dbo].[FilterServiceTickets]
-- Add the parameters for the stored procedure here
@userName varchar(20)= NULL,
@startDate datetime =NULL ,
@endDate datetime = NULL,
@status int = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT SERVICE_TICKET.SERVICE_TICKET_CODE, SERVICE_TICKET.PROBLEM_REPORTED, SERVICE_TICKET.CONTACT_NUMBER,
SERVICE_TICKET.STATUS
FROM aspnet_Users INNER JOIN
BuildingAddress ON aspnet_Users.UserId = BuildingAddress.UserID INNER JOIN
SERVICE_TICKET ON BuildingAddress.Customer_Site_Int_ID = SERVICE_TICKET.CUST_SITE_INT_ID
WHERE ((aspnet_Users.UserName = @userName)OR (@userName IS NULL))
AND ((SERVICE_TICKET.STATUS = @status) OR (status IS NULL))
AND ((SERVICE_TICKET.CREATED_DATE >=@startDate) OR (@startDate=NULL))AND ((SERVICE_TICKET.CREATED_DATE <=@endDate) OR (@endDate=NULL))END[/code]

You cant compare a value to NULL. NULL is not stored internally as a value but its a bit field set to indicate 'value not known' status. so always use IS NULL/IS NOT NULL rather than using operators =,<> ..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources