USE [ReplacementSchedule]
GO
/****** Object: StoredProcedure [dbo].[OrdersFilterQuery] Script Date: 12/31/2012 10:05:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[OrdersFilterQuery]
(
@dateStart datetime2='1900-00-00',
@dateEnd datetime2='2100-00-00',
@orderNumber int = 0,
@deptCode nvarchar(255) = NULL
)
AS
BEGIN
SELECT
Orders.OrderID,
Orders.OrderName,
Departments.DeptDescription AS Department,
Departments.DeptNumber AS DepartmentID,
Orders.Active AS IsActive,
Orders.DivisionDepartment,
Orders.Date,
Orders.CreatorID, Orders.LastOpID, CONVERT (VARCHAR(19),
Orders.LastOpDate, 120) AS LastOpDate,
Departments.DeptCode,
Departments.DeptCodeDesc FROM
Departments INNER JOIN Orders ON Departments.DeptNumber = Orders.DivisionDepartment
WHERE
(( (Orders.OrderID in ('%' + @orderNumber+ '%')) AND
(Orders.Date BETWEEN @dateStart AND @dateEnd) OR
(Departments.DeptCode = @deptCode)))
ORDER BY Orders.OrderID ASC
END
if ordernumber = 0 by default then select all orders
(Orders.OrderID in ('%' + @orderNumber+ '%'))
and if DeptCode is not null then select on deptCode otherwise select all if null.
You can use if to tell whether the value is null or not and do the following things:
USE [ReplacementSchedule]
GO
/****** Object: StoredProcedure [dbo].[OrdersFilterQuery] Script Date: 12/31/2012 10:05:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[OrdersFilterQuery]
(
@dateStart datetime2='1900-00-00',
@dateEnd datetime2='2100-00-00',
@orderNumber int = 0,
@deptCode nvarchar(255) = NULL
)
AS
BEGIN
if(@deptCode Is Null) then SELECT
Orders.OrderID,
Orders.OrderName,
Departments.DeptDescription AS Department,
Departments.DeptNumber AS DepartmentID,
Orders.Active AS IsActive,
Orders.DivisionDepartment,
Orders.Date,
Orders.CreatorID, Orders.LastOpID, CONVERT (VARCHAR(19),
Orders.LastOpDate, 120) AS LastOpDate,
Departments.DeptCode,
Departments.DeptCodeDesc FROM
Departments INNER JOIN Orders ON Departments.DeptNumber = Orders.DivisionDepartment
WHERE
(( (Orders.OrderID in ('%' + @orderNumber+ '%')) AND
(Orders.Date BETWEEN @dateStart AND @dateEnd) OR
(Departments.DeptCode = @deptCode)))
ORDER BY Orders.OrderID ASC
else Select * from …… END
if ordernumber = 0 by default then select all orders
(Orders.OrderID in ('%' + @orderNumber+ '%'))
macupryk
Member
716 Points
352 Posts
need to fix query
Dec 31, 2012 08:01 PM|LINK
USE [ReplacementSchedule] GO /****** Object: StoredProcedure [dbo].[OrdersFilterQuery] Script Date: 12/31/2012 10:05:21 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[OrdersFilterQuery] ( @dateStart datetime2='1900-00-00', @dateEnd datetime2='2100-00-00', @orderNumber int = 0, @deptCode nvarchar(255) = NULL ) AS BEGIN SELECT Orders.OrderID, Orders.OrderName, Departments.DeptDescription AS Department, Departments.DeptNumber AS DepartmentID, Orders.Active AS IsActive, Orders.DivisionDepartment, Orders.Date, Orders.CreatorID, Orders.LastOpID, CONVERT (VARCHAR(19), Orders.LastOpDate, 120) AS LastOpDate, Departments.DeptCode, Departments.DeptCodeDesc FROM Departments INNER JOIN Orders ON Departments.DeptNumber = Orders.DivisionDepartment WHERE (( (Orders.OrderID in ('%' + @orderNumber+ '%')) AND (Orders.Date BETWEEN @dateStart AND @dateEnd) OR (Departments.DeptCode = @deptCode))) ORDER BY Orders.OrderID ASC END if ordernumber = 0 by default then select all orders (Orders.OrderID in ('%' + @orderNumber+ '%')) and if DeptCode is not null then select on deptCode otherwise select all if null.Careed
All-Star
18764 Points
3637 Posts
Re: need to fix query
Jan 01, 2013 12:50 AM|LINK
As this is stored procedure, only the SQL between the BEGIN and the END will be executed. Thus, your END statement needs to be at the very end.
"The oxen are slow, but the earth is patient."
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: need to fix query
Jan 01, 2013 07:54 AM|LINK
Hi,
You can use if to tell whether the value is null or not and do the following things:
USE [ReplacementSchedule] GO /****** Object: StoredProcedure [dbo].[OrdersFilterQuery] Script Date: 12/31/2012 10:05:21 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[OrdersFilterQuery] ( @dateStart datetime2='1900-00-00', @dateEnd datetime2='2100-00-00', @orderNumber int = 0, @deptCode nvarchar(255) = NULL ) AS BEGIN if(@deptCode Is Null) then SELECT Orders.OrderID, Orders.OrderName, Departments.DeptDescription AS Department, Departments.DeptNumber AS DepartmentID, Orders.Active AS IsActive, Orders.DivisionDepartment, Orders.Date, Orders.CreatorID, Orders.LastOpID, CONVERT (VARCHAR(19), Orders.LastOpDate, 120) AS LastOpDate, Departments.DeptCode, Departments.DeptCodeDesc FROM Departments INNER JOIN Orders ON Departments.DeptNumber = Orders.DivisionDepartment WHERE (( (Orders.OrderID in ('%' + @orderNumber+ '%')) AND (Orders.Date BETWEEN @dateStart AND @dateEnd) OR (Departments.DeptCode = @deptCode))) ORDER BY Orders.OrderID ASC else Select * from …… END if ordernumber = 0 by default then select all orders (Orders.OrderID in ('%' + @orderNumber+ '%'))