I have a simple store procedure and I'm pulling the results into CR. It works great but I need to be able to allow the user to select multiple product numbers, if they want to. Sometimes they need sales for 1 product number, sometimes that have 10 or 12 products they need sales on. Right now they can only select one product at a time. How can I make it take 1 product or multiple products? thanks for the help...
CREATE PROCEDURE SQLUSER.ProductSales
@StartDate datetime,
@EndDate datetime,
@ProductNo numeric
AS
SELECT dbo.TicketDetail.ProductNo AS Number, SUM(dbo.TicketDetail.Dollars) AS Dollar, SUM(dbo.TicketDetail.Units) AS Unit,
dbo.Products.Description
FROM dbo.TicketDetail INNER JOIN dbo.Products ON dbo.TicketDetail.LocationId = dbo.Products.LocationId AND dbo.TicketDetail.ProductNo = dbo.Products.ProductNo
WHERE (dbo.TicketDetail.ProductNo =@ProductNo) AND (dbo.TicketDetail.TicketDate BETWEEN @StartDate and @EndDate)
GROUP BY dbo.TicketDetail.ProductNo, dbo.Products.Description
ORDER BY dbo.TicketDetail.ProductNo
CREATE FUNCTION dbo.ParmsToList (@Parameters varchar(500))
returns @result TABLE (Value varchar(30))
AS
begin
DECLARE @TempList table
(
Value varchar(30)
)
DECLARE @Value varchar(30), @Pos int
SET @Parameters = LTRIM(RTRIM(@Parameters))+
SET @Pos = CHARINDEX(',', @Parameters, 1)
IF REPLACE(@Parameters, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @Value = LTRIM(RTRIM(LEFT(@Paramete
IF @Value <> ''
BEGIN
INSERT INTO @TempList (Value) VALUES (@Value) --Use Appropriate conversion
END
SET @Parameters = RIGHT(@Parameters, LEN(@Parameters) - @Pos)
SET @Pos = CHARINDEX(',', @Parameters, 1)
END
END
INSERT @result
SELECT value
FROM @TempList
RETURN
END
and change your procedure into:
CREATE PROCEDURE SQLUSER.ProductSales
@StartDate datetime,
@EndDate datetime,
@ProductNo varchar(8000)
AS
SELECT dbo.TicketDetail.ProductNo
dbo.Products.Description
FROM dbo.TicketDetail INNER JOIN dbo.Products ON dbo.TicketDetail.LocationI
WHERE dbo.TicketDetail.ProductNo
AND dbo.TicketDetail.TicketDat
GROUP BY dbo.TicketDetail.ProductNo
ORDER BY dbo.TicketDetail.ProductNo
and call the procedure will a comma-separated list of productno values (no spaces) as a single string.
exec SQLUSER.ProductSales '2006-10-01', '2006-11-01', '2342,24234,22313,32'