Hi.
I am using below code.
CREATE FUNCTION dbo.SplitOrderIDs
(
@OrderList varchar(500)
)
RETURNS
@ParsedList table
(
OrderID int
)
AS
BEGIN
DECLARE @OrderID varchar(10), @Pos int
SET @OrderList = LTRIM(RTRIM(@OrderList))+ ','
SET @Pos = CHARINDEX(',', @OrderList, 1)
IF REPLACE(@OrderList, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @OrderID = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1)))
IF @OrderID <> ''
BEGIN
INSERT INTO @ParsedList (OrderID)
VALUES (CAST(@OrderID AS int))
END
SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos)
SET @Pos = CHARINDEX(',', @OrderList, 1)
END
END
RETURN
END
GO
CREATE PROC dbo.GetOrderList6
(
@OrderList varchar(500)
)
AS
BEGIN
SET NOCOUNT ON
SELECT o.OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders AS o
JOIN
dbo.SplitOrderIDs(@OrderList) AS s
ON
o.OrderID = s.OrderID
END
GO
GRANT EXEC ON dbo.GetOrderList6 TO WebUser
GO
But if I pass @OrderList NULL or blank then it returns nothing. My requirement is if @orderlist has value then return only those records and if it is null or blank then returns all records.
Its returning records if I pass some value in @orderList but problem is when @OrderList is null or blank.
Thanks