Passing a specific cursor record to a function

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,
VSHi

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/techin.../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/techin.../2000/books.asp