Hello,

Is it possible? Can I select a specific record of the cursor to be
sent to a seperate function to do all the computations etc.?

Regards,
VS

Hi

You will have to pass each as a separate variable or possibly use a
(temporary) table. If you can rewrite the cursor to be a set function you
will usually get much better performance.

John

"TinTin" <lalalulu24@yahoo.com> wrote in message
news:2d5425d1.0406151234.3925efae@posting.google.c om...
> Hello,
>
> Is it possible? Can I select a specific record of the cursor to be
> sent to a seperate function to do all the computations etc.?
>
> Regards,
> VS

TinTin (lalalulu24@yahoo.com) writes:
> Is it possible? Can I select a specific record of the cursor to be
> sent to a seperate function to do all the computations etc.?

You can pass a cursor varible to stored procedure, but I am not sure that
functions accept cursor variables.

In any case, cursors is not something you should use that often. As I said
in my other posting, work set-based whenever possible.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Hi
John: I think I will buy the Temporary Table suggestion. Thanks!

Erland: I am not too sure about what you mean by "work set-based".
There might be an easier alternative to what I am doing. Could you
precisely refer to me a specific area; or topic which I should read?

Regards!


Erland Sommarskog <esquel@sommarskog.se> wrote in message news:<Xns950A2ABF8BDEYazorman@127.0.0.1>...
> TinTin (lalalulu24@yahoo.com) writes:
> > Is it possible? Can I select a specific record of the cursor to be
> > sent to a seperate function to do all the computations etc.?
>
> You can pass a cursor varible to stored procedure, but I am not sure that
> functions accept cursor variables.
>
> In any case, cursors is not something you should use that often. As I said
> in my other posting, work set-based whenever possible.

Hi

You would have to post the DDL (Create table statements etc), Example Data
as Insert statements and expected output along with your stored procedure
definition, so that we have a better idea what you are trying to do.

John

"TinTin" <lalalulu24@yahoo.com> wrote in message
news:2d5425d1.0406160504.66185536@posting.google.c om...
> Hi
> John: I think I will buy the Temporary Table suggestion. Thanks!
>
> Erland: I am not too sure about what you mean by "work set-based".
> There might be an easier alternative to what I am doing. Could you
> precisely refer to me a specific area; or topic which I should read?
>
> Regards!
>
>
> Erland Sommarskog <esquel@sommarskog.se> wrote in message
news:<Xns950A2ABF8BDEYazorman@127.0.0.1>...
> > TinTin (lalalulu24@yahoo.com) writes:
> > > Is it possible? Can I select a specific record of the cursor to be
> > > sent to a seperate function to do all the computations etc.?
> >
> > You can pass a cursor varible to stored procedure, but I am not sure
that
> > functions accept cursor variables.
> >
> > In any case, cursors is not something you should use that often. As I
said
> > in my other posting, work set-based whenever possible.

TinTin (lalalulu24@yahoo.com) writes:
> Erland: I am not too sure about what you mean by "work set-based".
> There might be an easier alternative to what I am doing. Could you
> precisely refer to me a specific area; or topic which I should read?

Rather than writing:


DECLARE @price money,
@qty int,
@total money,
@orderid int,
@prev_orderid int

DECLARE order_total_cur INSENSITIVE CURSOR FOR
SELECT orderid, price, qty
FROM order_details
ORDER BY orderid

OPEN order_total_cur
SELECT @total = 0
WHILE 1 = 1
BEGIN
FETCH order_total_cur INTO @orderid, @price, @qty
IF @@fetch_status <> 0
BREAK

IF @prev_orderid IS NOT NULL AND @orderid <> @prev_orderid
BEGIN
UPDATE orders
SET total = @total
WHERE orderid = @prev_orderid

SELECT @total = 0
END

SELECT @total = @total + @price * @qty, @prev_orderid = @orderid
END

DEALLOCATE order_total_cur

IF @orderid IS NOT NULL
BEGIN
UPDATE orders
SET total = @total
WHERE orderid = @orderid
END

You write:

UPDATE orders
SET total = od.total
FROM orders o
JOIN (SELECT orderid, total = sum(qty * price)
FROM orderdetails
GROUP BY orderid) AS od ON o.orderid = od.orderid

Not only is this more concise and less error-prone to write, the
difference in performance could be magnirute if there are many
rows in the table.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp