possible to pass param to cursor thru stored procedure?

[Samoyed]

Hi,

I'm just curious if this will work, or if there is a better way to do this. When I run just the cursor alone and hard code the StockistID, my cursor works. When I put this cursor into a stored procedure and pass a param to this cursor, it is not recognized Does anyone know how I can pass a paramater from a stored procedure to a cursor? Thx in advance. Code is below and does NOT work:

Create Procedure spMobileStockistSalesForSelectedMonthAndYear
(
@ParamStockistID varchar(25)
)
As

GO
--declare all variables
Declare @ReceiptNumber varchar(11)
--here we define the cursor
DECLARE authors_cursor CURSOR FOR
SELECT ReceiptNumber, Sum(tblOrders.Quantity) AS SumOfQuantity
FROM tblStockist INNER JOIN tblOrders ON tblStockist.StockistID = tblOrders.StockistID
GROUP BY tblOrders.ReceiptNumber
HAVING (((tblStockist.StockistID)=@ParamStockistID) AND ((tblStockist.Code)<>'CA76969980'))
ORDER BY tblOrders.ReceiptNumber

 

[philhege]

The DECLARE CURSOR statement works at a different level from the one at which your SP is working. You'd have to do dynamic SQL to pass the value to the statement.

HTH,

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'm not as think as you confused I am.
-----------
Flabbergasted (a.): Amazed at how much weight one has gained.
-----------
Oyster (n.): One who sprinkles their conversation with Yiddish expressions.

 

[amrita418]

I think you have the filters on the having clause when they are expected to be in the where clause. Try changing that and see what you get.

DECLARE  authors_cursor CURSOR FOR
			SELECT 		ReceiptNumber, 
					Sum(tblOrders.Quantity) AS SumOfQuantity
			FROM 		tblStockist 
					INNER JOIN tblOrders 
					ON tblStockist.StockistID = tblOrders.StockistID
			where 		tblStockist.StockistID = @ParamStockistID 
					and tblStockist.Code)<>'CA76969980'
			GROUP BY 	tblOrders.ReceiptNumber
			ORDER BY 	tblOrders.ReceiptNumber

Regards,
AA