Passing Cursor as SP output parameter

Hi,
I have a SP that uses a temporary table and cursor to give all employee IDs
for a passwed managerid at all level of a hierarchy using a structuretable
that has parentId and childId. The SP runs successfully, but when I am
trying to use the output cursor I get the following error:
Calling SQL:
DECLARE @.childCursor cursor
EXEC dco.getPersonIdChild 'EMGR', 'STR', 6852, @.childCursor OUT
FETCH @.childCursor
CLOSE @.childCursor
DEALLOCATE @.ChildCursor
output:
The variable '@.childCursor' does not currently have a cursor allocated to it
.
Server: Msg 16950, Level 16, State 2, Line 4
The variable '@.childCursor' does not currently have a cursor allocated to it
.
Server: Msg 16950, Level 16, State 2, Line 5
The variable '@.childCursor' does not currently have a cursor allocated to it
.
Any help on this problem will be greatly appreciated.
SangDid you define the childCuror variable as an OUT parm in the proc?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sang" <Sang@.discussions.microsoft.com> wrote in message
news:C7D4D59F-3E84-42BC-B132-67DAF449AB6B@.microsoft.com...
> Hi,
> I have a SP that uses a temporary table and cursor to give all employee ID
s
> for a passwed managerid at all level of a hierarchy using a structuretable
> that has parentId and childId. The SP runs successfully, but when I am
> trying to use the output cursor I get the following error:
> Calling SQL:
> DECLARE @.childCursor cursor
> EXEC dco.getPersonIdChild 'EMGR', 'STR', 6852, @.childCursor OUT
> FETCH @.childCursor
> CLOSE @.childCursor
> DEALLOCATE @.ChildCursor
> output:
> The variable '@.childCursor' does not currently have a cursor allocated to
it.
> Server: Msg 16950, Level 16, State 2, Line 4
> The variable '@.childCursor' does not currently have a cursor allocated to
it.
> Server: Msg 16950, Level 16, State 2, Line 5
> The variable '@.childCursor' does not currently have a cursor allocated to
it.
> Any help on this problem will be greatly appreciated.
> Sang
>|||Yes, please see the following:
ALTER PROCEDURE dco.getPersonIdChild (
@.Code CodeExtraLong,
@.CodeTypeCode CHAR(3),
@.PersonIdParent UniqueId,
@.childCursor CURSOR VARYING OUT
)
AS
"Tibor Karaszi" wrote:

> Did you define the childCuror variable as an OUT parm in the proc?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Sang" <Sang@.discussions.microsoft.com> wrote in message
> news:C7D4D59F-3E84-42BC-B132-67DAF449AB6B@.microsoft.com...
>|||I think you need to give us more to go on. Below work fine for me:
USE pubs
GO
CREATE PROC p @.c cursor varying OUT
AS
SET @.c = CURSOR LOCAL FOR SELECT au_lname FROM authors
OPEN @.c
GO
DECLARE @.oc cursor
EXEC p @.c = @.oc OUTPUT
FETCH NEXT FROM @.oc
GO
DROP PROC p
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sang" <Sang@.discussions.microsoft.com> wrote in message
news:1E16242A-4AD9-4F61-8781-13F833E85369@.microsoft.com...
> Yes, please see the following:
> ALTER PROCEDURE dco.getPersonIdChild (
> @.Code CodeExtraLong,
> @.CodeTypeCode CHAR(3),
> @.PersonIdParent UniqueId,
> @.childCursor CURSOR VARYING OUT
> )
> AS
> "Tibor Karaszi" wrote:
>|||The SP is the following:
ALTER PROCEDURE dco.getPersonIdChild (
@.Code CodeExtraLong,
@.CodeTypeCode CHAR(3),
@.PersonIdParent UniqueId,
@.childCursor CURSOR VARYING OUT
)
AS
BEGIN
DECLARE
@.Level smallint,
@.PersonIdParentC UniqueID
-- Let the intial set of children for the passed parentId
SET @.Level = 1
IF OBJECT_ID ('#tempTable') IS NOT NULL
Drop Table #tempTable
CREATE TABLE #TempTable (PersonIdChild UniqueId, Depth smallint, Checked
bit)
INSERT INTO #TempTable
SELECT PersonIdChild, @.Level AS "Depth", 0 AS "Checked"
FROM PersonStructures
WHERE Code = @.Code
AND CodeTypeCode = @.CodeTypeCode
AND PersonIdParent = @.PersonIdParent
AND EndDate is NULL
WHILE ((SELECT count(*) from #TempTable WHERE Checked = 0 ) > 0)
BEGIN
DECLARE Children CURSOR
FOR
SELECT PersonIdChild
FROM #TempTable
WHERE Depth = @.Level
OPEN Children
FETCH Children into @.PersonIdParentC
WHILE (@.@.FETCH_STATUS =0)
BEGIN
-- PRINT '@.Level is ' + CAST(@.Level AS CHAR)
IF ((SELECT Count(*) from PersonStructures
WHERE Code = @.Code
AND CodeTypeCode = @.CodeTypeCode
AND PersonIdParent = @.PersonIdParentC
AND EndDate is NULL) > 0 )
BEGIN
INSERT INTO #TempTable
SELECT PersonIdChild, @.Level + 1, 0
FROM PersonStructures
WHERE Code = @.Code
AND CodeTypeCode = @.CodeTypeCode
AND PersonIdParent = @.PersonIdParentC
AND EndDate is NULL
END
-- Now Update the checked column for the record just processed
UPDATE #TempTable
SET Checked = 1
WHERE PersonIdChild = @.PersonIdParentC
FETCH NEXT FROM Children INTO @.PersonIdParentC
END
CLOSE Children
DEALLOCATE Children
SET @.Level = @.Level + 1
END
DECLARE s CURSOR
LOCAL
FOR SELECT PersonIdChild, Depth
FROM #TempTable
SET @.childCursor = s
OPEN @.childCursor
RETURN (0)
END
"Tibor Karaszi" wrote:

> I think you need to give us more to go on. Below work fine for me:
> USE pubs
> GO
> CREATE PROC p @.c cursor varying OUT
> AS
> SET @.c = CURSOR LOCAL FOR SELECT au_lname FROM authors
> OPEN @.c
> GO
> DECLARE @.oc cursor
> EXEC p @.c = @.oc OUTPUT
> FETCH NEXT FROM @.oc
> GO
> DROP PROC p
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Sang" <Sang@.discussions.microsoft.com> wrote in message
> news:1E16242A-4AD9-4F61-8781-13F833E85369@.microsoft.com...
>sql

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

Pass a value dinamically into a cursor

Hello,
I need to pass a [server_name] value dinamically to the=20
statement used in the definition of one cursor but...=20
don=B4t know how.
I need use server_name dinamically:
declare login_cur cursor for
select [name]
from [server_name].master.dbo.syslogins
Best regards
How about creating a stored procedure that looks like this? --
create procedure serversproc
@.sname char(20)
as
exec ('select * from ' + @.sname +'.master.dbo.sysdatabases')
go
exec serversproc MyServerName
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Pass a value dinamically into a cursor

Hello,
I need to pass a [server_name] value dinamically to the=20
statement used in the definition of one cursor but...=20
don=B4t know how.
I need use server_name dinamically:
declare login_cur cursor for
select [name]
from [server_name].master.dbo.syslogins
Best regardsHow about creating a stored procedure that looks like this? --
create procedure serversproc
@.sname char(20)
as
exec ('select * from ' + @.sname +'.master.dbo.sysdatabases')
go
exec serversproc MyServerName
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Pass a value dinamically into a cursor

Hello,
I need to pass a [server_name] value dinamically to the statement used in the definition of one cursor but... don=B4t know how.
I need use server_name dinamically:
declare login_cur cursor for
select [name]
from [server_name].master.dbo.syslogins
Best regardsHow about creating a stored procedure that looks like this? --
create procedure serversproc
@.sname char(20)
as
exec ('select * from ' + @.sname +'.master.dbo.sysdatabases')
go
exec serversproc MyServerName
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.