Clicky

I have a situation where I am generating a report where the data is returned from SQL tables.  Each row is a category and each column is a count of the number of items sold for that category. I am trying to create a stored procedure that I can call 1 time for each row and get back all 10 counts versus having to call the SP 10 times for each row.

Here is a scaled back version of what I have so far.  The actual SP has a total of 10 items counts being passed in.

CREATE        PROCEDURE [dbo].[RPT_ProductCount]

      @CATEGORY                        VARCHAR(10),
      @ITEMCOUNT1_IN                  INT,
      @ITEMCOUNT2_IN                  INT

AS

DECLARE
      @ITEMCOUNT1_OUT            INT,
      @CATEGORYNAME1_OUT      VARCHAR(100),
      @ITEMCOUNT2_OUT            INT,
      @CATEGORYNAME2_OUT      VARCHAR(100)

SELECT
      @ITEMCOUNT1_OUT = COUNT(CategoryCode), @CATEGORYNAME1_OUT = CategoryName
FROM
      ProductsSales

WHERE
      CategoryCode = @CATEGORY AND
      COUNT(CategoryCode) = @ITEMCOUNT1_IN

GROUP BY
      CategoryCode

ORDER BY
      CategoryCode
      

SELECT
      @ITEMCOUNT2_OUT = COUNT(CategoryCode), @CATEGORYNAME2_OUT = CategoryName
FROM
      ProductsSales

WHERE
      CategoryCode = @CATEGORY AND
      COUNT(CategoryCode) = @ITEMCOUNT2_IN

GROUP BY
      CategoryCode

ORDER BY
      CategoryCode



I am calling the SP from ASP as follows:
Set rsProductCount = DataConn.Exec(“RPT_ProductCount “110-01”, 25, 50”)  

I get the following error:
Operation is not allowed when the object is closed


When I run the query from query analyzer I get no data in the results window, just Command(s) completed successfully.

I thought I would get a recordset back with my data.


If I run the single query for count total item sold of 25 I get:

Count            Category Name
13            Product 1

If I run the single query for count total item sold of 50 I get:

Count            Category Name
22            Product 2


The desired result is a record set as follows:

Count            Category Name
13            Product 1
22            Product 2


What changes do I need to make to stored procedure to get this?

Thanks,
David

asked 12/11/2011 04:18

skinsfan99's gravatar image

skinsfan99 ♦♦


7 Answers:
Its happening because you are assigning data to variables but not directly fetching the data to display

SELECT
      @ITEMCOUNT1_OUT = COUNT(CategoryCode), @CATEGORYNAME1_OUT = CategoryName
FROM
      ProductsSales

WHERE
      CategoryCode = @CATEGORY AND
      COUNT(CategoryCode) = @ITEMCOUNT1_IN

GROUP BY
      CategoryCode

ORDER BY
      CategoryCode


If you don't want those parameters, you can simply remove them

SELECT
      ITEMCOUNT1_OUT = COUNT(CategoryCode), CATEGORYNAME1_OUT = CategoryName
FROM
      ProductsSales

WHERE
      CategoryCode = @CATEGORY AND
      COUNT(CategoryCode) = @ITEMCOUNT1_IN

GROUP BY
      CategoryCode

ORDER BY
      CategoryCode

------------
or print the variable after the values are assined as:-

SELECT       @ITEMCOUNT1_OUT,@CATEGORYNAME1_OUT
link

answered

TempDBA's gravatar image

TempDBA

Try this.

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
CREATE PROCEDURE [dbo].[RPT_ProductCount]
 @CATEGORY VARCHAR(10),
 @ITEMCOUNT1_IN INT,
 @ITEMCOUNT2_IN INT
AS
BEGIN
	SET NOCOUNT ON

	DECLARE 
	 @ITEMCOUNT1_OUT INT,
	 @CATEGORYNAME1_OUT VARCHAR(100),
	 @ITEMCOUNT2_OUT INT,
	 @CATEGORYNAME2_OUT VARCHAR(100)

	SELECT 
	 @ITEMCOUNT1_OUT = COUNT(CategoryCode), @CATEGORYNAME1_OUT = CategoryName
	FROM 
	 ProductsSales
	WHERE
	 CategoryCode = @CATEGORY AND
	 COUNT(CategoryCode) = @ITEMCOUNT1_IN
	GROUP BY
	 CategoryCode
	ORDER BY
	 CategoryCode
	 

	SELECT 
	 @ITEMCOUNT2_OUT = COUNT(CategoryCode), @CATEGORYNAME2_OUT = CategoryName
	FROM 
	 ProductsSales
	WHERE
	 CategoryCode = @CATEGORY AND
	 COUNT(CategoryCode) = @ITEMCOUNT2_IN
	GROUP BY
	 CategoryCode
	ORDER BY
	 CategoryCode

	SELECT @ITEMCOUNT1_OUT, @CATEGORYNAME1_OUT, @ITEMCOUNT2_OUT INT, @CATEGORYNAME2_OUT
END
GO
link

answered 2011-12-12 at 00:37:56

hongjun's gravatar image

hongjun

You basically need to have

SET NOCOUNT ON

and also select out the values

SELECT @ITEMCOUNT1_OUT, @CATEGORYNAME1_OUT, @ITEMCOUNT2_OUT INT, @CATEGORYNAME2_OUT
link

answered 2011-12-12 at 00:45:38

hongjun's gravatar image

hongjun

Both your solutions work perfectly!!  I will make sure to award points equally.

Sorry for the delay in getting back to you.  I have been waiting to meet with the user.  They have requested some addtional columns to be returned in the dataset.  These columns now create a situation where there could be more than one record returned for each select statement.  Using your solution I am only getting the last record returned for each select.   As an example:


If I run the single query for count total item sold of 25 I get:

Count            Category Name        Sub-Category
7                    Product 1                 Product 1-a
8                    Product 1                 Product 1-b

If I run the single query for count total item sold of 50 I get:

Count            Category Name        Sub-Category
10                  Product 2                 Product 2-a
6                    Product 2                 Product 2-b
7                    Product 2                 Product 2-c

Running you solution I get back this, which is the last record in each dataset
8                    Product 1                 Product 1-b
7                    Product 2                 Product 2-c


The desired result is a record set as follows:

Count            Category Name        Sub-Category
7                    Product 1                 Product 1-a
8                    Product 1                 Product 1-b
10                  Product 2                 Product 2-a
6                    Product 2                 Product 2-b
7                    Product 2                 Product 2-c

What change do I need to make to get back all records?

Thanks again for your assistance!!
David
link

answered 2011-12-12 at 00:46:20

skinsfan99's gravatar image

skinsfan99

What is your sql now?

You can consider merging them to a temp table and then select * from it.

E.g.

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
set nocount on
declare @tblResult table ([Count] int, [CategoryName] varchar(255), [SubCategory] varchar(255))
--first query
insert into @tblResult
	select 1, 'first', 'first'
--second query
insert into @tblResult
	select 2, 'second', 'second'
--return as one resultset
select [Count], [CategoryName], [SubCategory] from @tblResult
link

answered 2011-12-14 at 08:14:21

hongjun's gravatar image

hongjun

Thank you so much for your help!!

I have one more question reqarding do a For/Next type loop in a SP.  Can you answer that here or should I enter a new question?
link

answered 2011-12-14 at 18:06:35

skinsfan99's gravatar image

skinsfan99

new question. post the url here once posted.
link

answered 2011-12-14 at 20:23:07

hongjun's gravatar image

hongjun

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

×10
×4
×6

Asked: 12/11/2011 04:18

Seen: 271 times

Last updated: 12/14/2011 12:23