Ideally, we should base our database queries on single valued criterias, let's say all the items from the OrderID 23. But if one of our SELECT stored procedures requires a multiple valued criteria, it should accept it as an NTEXT comma-separated list :
CREATE PROCEDURE [dbo].[Item_SelectByOrderIDs]
(
@OrderIDs NTEXT
)
This stored procedure could then be called with the '1,5,7,' parameter in order to retrieve the items from the OrderIDs 1, 5 and 7. Within the stored procedure, the homemade SplitIDs utility function is used to parse these values back from the NTEXT parameter :
SELECT DISTINCT Item.*
FROM Item INNER JOIN OrderItem ON Item.ItemID=OrderItem.ItemID
WHERE OrderItem.OrderID IN
(
SELECT * FROM SplitIDs(@OrderIDs)
)
Subscribe to:
Post Comments (Atom)
1 comment:
CREATE FUNCTION [dbo].[SplitIDs]
(
@ListOfIDs NTEXT
)
RETURNS @IDs TABLE
(
ID INT
)
AS
BEGIN
DECLARE @CurrentPosition AS INT
DECLARE @NextPosition AS INT
SET @CurrentPosition = 1;
SET @NextPosition = CHARINDEX(',', @ListOfIDs, @CurrentPosition);
WHILE (@NextPosition <> 0)
BEGIN
DECLARE @CurrentID AS INT;
SET @CurrentID = SUBSTRING(@ListOfIDs, @CurrentPosition, @NextPosition-@CurrentPosition);
INSERT INTO @IDs VALUES (@CurrentID);
SET @CurrentPosition = @NextPosition + 1;
SET @NextPosition = CHARINDEX(',', @ListOfIDs, @CurrentPosition);
END
RETURN
END
Post a Comment