Solved

A Stored Procedured w/ Multiple Parameters

Posted on 2006-11-10
4
233 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:DFCRJ
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 17913671
create the following function:


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(@Parameters, @Pos - 1)))
               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 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 in ( select * from dbo.ParmsToList ( @ProductNo ))
AND  dbo.TicketDetail.TicketDate BETWEEN @StartDate and @EndDate
GROUP BY  dbo.TicketDetail.ProductNo, dbo.Products.Description
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'
0
 
LVL 11

Expert Comment

by:Otana
ID: 17913676
Use Dynamic SQL, user has to pass ProductNo in a string, like '10,20,30'.

CREATE PROCEDURE SQLUSER.ProductSales
     
     @StartDate datetime,
     @EndDate datetime,
     @ProductNo varchar(8000)

 AS
EXEC('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 IN (' + @ProductNo + ')) AND (dbo.TicketDetail.TicketDate BETWEEN @StartDate and @EndDate)
GROUP BY  dbo.TicketDetail.ProductNo, dbo.Products.Description
ORDER BY dbo.TicketDetail.ProductNo')
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17913687

DFCRJ,
> Right now they can only select one product at a time. How can I make  it take 1 product or multiple products?

You can build an xml at the front end and pass it to the sp. you need to modify the sp in order to handle the xml document
refer 'OPENXML' in books online , it contains few exaples
0
 

Author Comment

by:DFCRJ
ID: 17914362
I used the first example and it works great. I havent tried the other two but I will. I'm very appreciate the response from everyone!!
thanks.......
RJ
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question