Can I fill a cursor from a strored procedure?

Basically, I have a complex stored procedure that combines two tables and fills a cursor.

I would like to fill another cursor in another stored procedure from the results of this first stored proc, rather than have to type it all in again.

The reason being that I am doing a one time import of some data from two tables into one new table based on some complex linking/querying.

Can I fill a cursor from the output of another stored procedure rather than an inline SELECT statement?

Does the sp I am using have to have cursor as an out parameter?a more easier way would be to create a table with all the columns tht your first cursor returns...so when you combine the 2 tables and fill the cursor...insert those records into the table..now you have a table with the records...so you can use a select statement on the table itself in the second stored proc...

HTH|||I would rather use the table option. But since the tables are shared between stored procedures then you need to use the local temp table or global temp tables.

But there are options to pass a cursor back to the calling procedure ... You would need to use an output cursor parameter in your stored procedure. Note that cursor parameters can be only output & use the keyword VARYING since the resultset supported is created dynamically by the stored procedure and whose contents can vary. Something along the lines of

CREATE PROCEDURE usp_cursor
@.orders_cursor CURSOR VARYING OUTPUT
AS
SET @.orders_cursor = CURSOR FOR
SELECT *
FROM Northwind..Orders
WHERE Freight > 100.00
OPEN @.titles_cursor
GO

You can now execute this stored procedure in a batch which declares a local cursor and assign the output cursor parameter value to the local cursor variable like :

DECLARE @.local_crsr CURSOR
EXEC usp_cursor @.orders_cursor = @.local_crsr OUTPUT
WHILE @.@.FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM @.local_crsr
...
END
CLOSE @.local_crsr
DEALLOCATE @.local_crsr|||hi
i dont have any idea about the topic cursor,can you give me a certain link that it can help me to understand very well this topic and thank you for your help|||Article on cursorssql

Can I do this without a cursor?

Consider a table called Target with a character column called StringColumn.
I
also have a lookup table called ToReplace which contains 2 columns, OldValue
and NewValue. I'm trying to use the replace function to update the
StringColumn value in the Target table, finding the text in OldValue and
replacing it with the text in NewValue from the ToReplace table. The update
I'm using just grabs the first record from the ToReplace table. Can anyone
think of a way to do this in one update statement? Thanks in advance. Here i
s
some sloppy ddl, but it gives you the idea:
Create table ToReplace
(OldValue varchar(10),
NewValue varchar(10))
insert into ToReplace
values
('old1', 'new1')
insert into ToReplace
values
('old2', 'new2')
insert into ToReplace
values
('old3', 'new3')
Create table Target
(StringColumn varchar(50))
insert into target
values
('old1 some other text old3')
insert into target
values
('old3 old2 some other text')
select * from target
Update target
set StringColumn = replace(StringColumn, OldValue, NewValue)
from ToReplace
select * from targetI'm . Why are you just using a simple update
statement against the table instead of all this insert
and create table stuff?
Update target
set StringColumn = replace(StringColumn, OldValue, NewValue)
where some condition is true
Robbe Morris - 2004-2006 Microsoft MVP C#
Earn money answering .NET questions
http://www.eggheadcafe.com/forums/merit.asp
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:89BB53E2-C2BA-4BB5-9533-A940FFC3B403@.microsoft.com...
> Consider a table called Target with a character column called
> StringColumn. I
> also have a lookup table called ToReplace which contains 2 columns,
> OldValue
> and NewValue. I'm trying to use the replace function to update the
> StringColumn value in the Target table, finding the text in OldValue and
> replacing it with the text in NewValue from the ToReplace table. The
> update
> I'm using just grabs the first record from the ToReplace table. Can anyone
> think of a way to do this in one update statement? Thanks in advance. Here
> is
> some sloppy ddl, but it gives you the idea:
> Create table ToReplace
> (OldValue varchar(10),
> NewValue varchar(10))
> insert into ToReplace
> values
> ('old1', 'new1')
> insert into ToReplace
> values
> ('old2', 'new2')
> insert into ToReplace
> values
> ('old3', 'new3')
>
> Create table Target
> (StringColumn varchar(50))
> insert into target
> values
> ('old1 some other text old3')
> insert into target
> values
> ('old3 old2 some other text')
>
> select * from target
> Update target
> set StringColumn = replace(StringColumn, OldValue, NewValue)
> from ToReplace
> select * from target
>|||Now I'm . That insert and create table stuff is the ddl. If you run
it all you'll see my problem.
"Robbe Morris [C# MVP]" wrote:

> I'm . Why are you just using a simple update
> statement against the table instead of all this insert
> and create table stuff?
> Update target
> set StringColumn = replace(StringColumn, OldValue, NewValue)
> where some condition is true
>
> --
> Robbe Morris - 2004-2006 Microsoft MVP C#
> Earn money answering .NET questions
> http://www.eggheadcafe.com/forums/merit.asp
>
>
> "sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
> news:89BB53E2-C2BA-4BB5-9533-A940FFC3B403@.microsoft.com...
>
>|||The problem you have run into relates to why UPDATE has no FROM clause
in standard SQL.
A row in the table you are updating matches more than one row in the
table you are joining it to. However, the row you are updating is
always the same "before image" of that row. So one matching ToReplace
changes old1, another changes old2, but they BOTH change the ORIGINAL
image of the row in Target. So, the row in Target gets updated more
than once, but the result is any (unpredictable) ONE of the updates,
not all of them together.
I would simply run each value to be changed as a single UPDATE, with
hard-coded values, rather than use any sort of ToReplace table.
Otherwise you need to work in some sort of loop to apply only one
ToReplace row at a time.
Also, it would have been a good idea to add a WHERE clause to your
UPDATE:
where StringColumn like '%' + OldValue + '%'
Roy Harvey
Beacon Falls, CT
On Thu, 9 Mar 2006 13:39:27 -0800, sqlboy2000
<sqlboy2000@.discussions.microsoft.com> wrote:

>Consider a table called Target with a character column called StringColumn.
I
>also have a lookup table called ToReplace which contains 2 columns, OldValu
e
>and NewValue. I'm trying to use the replace function to update the
>StringColumn value in the Target table, finding the text in OldValue and
>replacing it with the text in NewValue from the ToReplace table. The update
>I'm using just grabs the first record from the ToReplace table. Can anyone
>think of a way to do this in one update statement? Thanks in advance. Here
is
>some sloppy ddl, but it gives you the idea:
>Create table ToReplace
>(OldValue varchar(10),
>NewValue varchar(10))
>insert into ToReplace
>values
>('old1', 'new1')
>insert into ToReplace
>values
>('old2', 'new2')
>insert into ToReplace
>values
>('old3', 'new3')
>
>Create table Target
>(StringColumn varchar(50))
>insert into target
>values
>('old1 some other text old3')
>insert into target
>values
>('old3 old2 some other text')
>
>select * from target
>Update target
>set StringColumn = replace(StringColumn, OldValue, NewValue)
>from ToReplace
>select * from target
>|||Thanks Robbe, I didn't see any elegant solution to this either. I'll just
loop through it, it's a nightly run, so it's not the end of the world.
"sqlboy2000" wrote:
> Now I'm . That insert and create table stuff is the ddl. If you ru
n
> it all you'll see my problem.
> "Robbe Morris [C# MVP]" wrote:
>|||Sorry, got your name wrong. Thank you Roy
"Roy Harvey" wrote:

> The problem you have run into relates to why UPDATE has no FROM clause
> in standard SQL.
> A row in the table you are updating matches more than one row in the
> table you are joining it to. However, the row you are updating is
> always the same "before image" of that row. So one matching ToReplace
> changes old1, another changes old2, but they BOTH change the ORIGINAL
> image of the row in Target. So, the row in Target gets updated more
> than once, but the result is any (unpredictable) ONE of the updates,
> not all of them together.
> I would simply run each value to be changed as a single UPDATE, with
> hard-coded values, rather than use any sort of ToReplace table.
> Otherwise you need to work in some sort of loop to apply only one
> ToReplace row at a time.
> Also, it would have been a good idea to add a WHERE clause to your
> UPDATE:
> where StringColumn like '%' + OldValue + '%'
> Roy Harvey
> Beacon Falls, CT
> On Thu, 9 Mar 2006 13:39:27 -0800, sqlboy2000
> <sqlboy2000@.discussions.microsoft.com> wrote:
>
>

Can Dynamic SQL be used to define a cursor

?
Just wondering...Hi
The coursor would only live within the context of the execute. If effect,
not usable.
Regards
Mike
"marcmc" wrote:

> ?
> Just wondering...|||thought so. Thx Mike.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> The coursor would only live within the context of the execute. If effect,
> not usable.
> Regards
> Mike
> "marcmc" wrote:
>|||Actually, it can be available outside the executed scope. Make sure it is a
global cursor. And, you
have to execute the whole lot, like:
EXEC('DECLARE c CURSOR FOR SELECT ...')
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:2DE21312-AC7D-4950-9A61-A6FC809ADFEA@.microsoft.com...
> thought so. Thx Mike.
> "Mike Epprecht (SQL MVP)" wrote:
>|||Although it is possible to do this hopefully you are aware that most of
the time cursors are a bad idea for data manipulation purposes -
usually there are better solutions and most people will avoid cursors
most of the time.
David Portas
SQL Server MVP
--|||Yes you can.
Example:
use northwind
go
declare @.i int
declare @.c cursor
exec sp_executesql N'set @.c = cursor fast_forward for select orderid from
northwind.dbo.orders open @.c', N'@.c cursor output', @.c output
if cursor_status('variable', '@.c') = 1
begin
while 1 = 1
begin
fetch next from @.c into @.i
if @.@.error != 0 or @.@.fetch_status != 0 break
print @.i
end
close @.c
deallocate @.c
end
go
I am not advocating for cursors.
AMB
"marcmc" wrote:

> ?
> Just wondering...

can cursor be nested?

Hi, I'm a newbie on using cursor. My question is if I create an update
trigger that loop through the Inserted rows by a cursor, and inside the
cursor loop, a stored procedure is executed, which contains a cursor loop
too, any problem about it?YEs of course, you can also nest cursors inline.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"nonno" <nonno@.discussions.microsoft.com> schrieb im Newsbeitrag
news:8EFFAC69-6544-4923-8A41-F228688A2F93@.microsoft.com...
> Hi, I'm a newbie on using cursor. My question is if I create an update
> trigger that loop through the Inserted rows by a cursor, and inside the
> cursor loop, a stored procedure is executed, which contains a cursor loop
> too, any problem about it?|||Thx for ur reply :) But if the outer cursor loop and the inner cursor loop
both access the same table, will deadlock occur?
"Jens Sü?meyer" wrote:

> YEs of course, you can also nest cursors inline.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "nonno" <nonno@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:8EFFAC69-6544-4923-8A41-F228688A2F93@.microsoft.com...
>
>|||Have a look at the Cursor option in BOL, you can handle the outside cusors
to behave as readonly if you need to. Rember that the default locking
beahviour is row locking, so even you will lock the data with anyother
option it depends on the the option wheter you lock one or multiple
datarows.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"nonno" <nonno@.discussions.microsoft.com> schrieb im Newsbeitrag
news:9D8BD362-E3F9-4E1A-80BF-819CD58B2D85@.microsoft.com...
> Thx for ur reply :) But if the outer cursor loop and the inner cursor loop
> both access the same table, will deadlock occur?
> "Jens Smeyer" wrote:
>|||another question:
if I open a cursor in a transaction and the transaction rollback, will the
cursor be automatically closed and deallocated?
"Jens Sü?meyer" wrote:

> Have a look at the Cursor option in BOL, you can handle the outside cusors
> to behave as readonly if you need to. Rember that the default locking
> beahviour is row locking, so even you will lock the data with anyother
> option it depends on the the option wheter you lock one or multiple
> datarows.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "nonno" <nonno@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:9D8BD362-E3F9-4E1A-80BF-819CD58B2D85@.microsoft.com...
>
>|||Because of the termination of the session inthat case, that would be the
effect.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"nonno" <nonno@.discussions.microsoft.com> schrieb im Newsbeitrag
news:C8DF1B00-5A10-413D-83A8-2986CBE6B4A2@.microsoft.com...
> another question:
> if I open a cursor in a transaction and the transaction rollback, will the
> cursor be automatically closed and deallocated?
> "Jens Smeyer" wrote:
>|||So to keep up your questions, it depends...
The inner Cursor will be closed when the session ends, the session ends when
the whole logic block is executed or an serverity error occured that kept
SQl Server from continuing the Cursor and the Transaction is ended, that the
fact if you call an procedure in the outer cursor which build up a cursor i
the prcedure)
Its easy to recode if you just write a simple cursor which call a procedure
and this sp establish a cursor which run into an error, try to declare the
cursor with the same name now from the QA.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"nonno" <nonno@.discussions.microsoft.com> schrieb im Newsbeitrag
news:C8DF1B00-5A10-413D-83A8-2986CBE6B4A2@.microsoft.com...
> another question:
> if I open a cursor in a transaction and the transaction rollback, will the
> cursor be automatically closed and deallocated?
> "Jens Smeyer" wrote:
>|||To add to the responses by Jens, it is often possible to use a set-based
processing rather than cursors. The set-based approach usually provides
better performance.
Hope this helps.
Dan Guzman
SQL Server MVP
"nonno" <nonno@.discussions.microsoft.com> wrote in message
news:8EFFAC69-6544-4923-8A41-F228688A2F93@.microsoft.com...
> Hi, I'm a newbie on using cursor. My question is if I create an update
> trigger that loop through the Inserted rows by a cursor, and inside the
> cursor loop, a stored procedure is executed, which contains a cursor loop
> too, any problem about it?|||But why would anyone write code like that in SQL? It is a XXXXX to
maintain, proprietary and each cursor is 1 to 2 orders of magnitude
slower than declarative SQL.
Post the DDL and a statement of the problem and you can get a better
answer.

Can a cursor variable be assigned to a dynamically named cursor?

(SQL Server 2000, SP3a)
(From a different thread.)
Hello, all!
I have an open global cursor that is created dynamically by stored procedure
A. I'd like
to reference this cursor from stored procedure B. I know the dynamic name o
f the cursor,
but I know of no way to get a "handle" of this cursor so that I can use it f
rom stored
procedure B in a cursor variable.
The [sp_describe_cursor] returns something called a cursor_handle. Can this
be used
somehow to set a cursor variable?
I thought maybe I could do something like this:
declare @.CursorName nvarchar(4000) select @.CursorName = 'cur'
execute
(
'
declare ' + @.CursorName + ' cursor forward_only read_only for
select name from sysobjects
'
)
declare @.Cursor cursor
declare @.Query nvarchar(4000)
select @.Query = 'set @.Cursor = ' + @.CursorName
execute [dbo].[sp_executesql] @.Query, N'@.Cursor cursor varying output', @.Cursor =
@.Cursor
output
execute('deallocate ' + @.CursorName)
But I get this error:
Server: Msg 181, Level 15, State 1, Line 1
Cannot use the OUTPUT option in a DECLARE statement.
Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable '@.Cursor'.
Which I don't fully understand. But, after some fiddling, it's clearly some
thing with the
[sp_executesql] line. No amount of massaging will get this to work -- my gu
ess is that
the structure of [sp_executesql] won't permit a cursor variable to be handle
d. :-(
Thanks for any help anyone can provide!
John PetersonCursors are usually best avoided because of their performance/resource
implications. Erland has an article on alternative methods for sharing data
between SPs:
http://www.sommarskog.se/share_data.html
For completeness, here's an amended version of your code:
...
SET @.query = 'SET @.cursor = ' + @.cursorname + ' OPEN @.cursor'
EXEC sp_executesql @.query, N' @.CURSOR CURSOR OUTPUT', @.cursor OUTPUT
...
Now reference the cursor by variable (@.cursor).
David Portas
SQL Server MVP
--|||David,
You're right -- I appreciate that cursors aren't wholly performant, but in m
y case, I'm
writing a management procedure that lends itself well to using cursors.
Thanks for the link on other techniques for sharing data. :-)
I think you solved my issue! From what I can tell, you merely removed the V
ARYING
keyword. From the stored procedure documentation (which I kind of assumed t
hat
[sp_executesql] was leveraging) it seemed as if the VARYING keyword was nece
ssary when
using a cursor variable. But, it appears not to be the case, and that was t
he one
combination I *didn't* try!
Thanks so much! :-)
John Peterson
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:gLedncWQLe7NqKrdRVn-hQ@.giganews.com...
> Cursors are usually best avoided because of their performance/resource
> implications. Erland has an article on alternative methods for sharing dat
a
> between SPs:
> http://www.sommarskog.se/share_data.html
> For completeness, here's an amended version of your code:
> ...
> SET @.query = 'SET @.cursor = ' + @.cursorname + ' OPEN @.cursor'
> EXEC sp_executesql @.query, N' @.CURSOR CURSOR OUTPUT', @.cursor OUTPUT
> ...
> Now reference the cursor by variable (@.cursor).
> --
> David Portas
> SQL Server MVP
> --
>|||Oddly, it seems like there are some things that can't be done with the curso
r variable.
For example, I tried:
open @.Cursor
But that doesn't appear to work. Only when the OPEN is in the context of th
e dynamic SQL
does it seem to open the cursor for the variable.
Additionally:
close @.Cursor
deallocate @.Cursor
Don't appear to work either. If I try and re-run my code snippet, it compla
ins that the
cursor still exists.
Unless the issue is that there are *two* "handles" to the same cursor (the o
riginal "By
Name" and the variable) -- and I need to essentially close both handles befo
re the cursor
will be destroyed?
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:u4t38hI%23DHA.888@.tk2msftngp13.phx.gbl...
> David,
> You're right -- I appreciate that cursors aren't wholly performant, but in
my case, I'm
> writing a management procedure that lends itself well to using cursors.
> Thanks for the link on other techniques for sharing data. :-)
> I think you solved my issue! From what I can tell, you merely removed the
VARYING
> keyword. From the stored procedure documentation (which I kind of assumed
that
> [sp_executesql] was leveraging) it seemed as if the VARYING keyword was ne
cessary when
> using a cursor variable. But, it appears not to be the case, and that was
the one
> combination I *didn't* try!
> Thanks so much! :-)
> John Peterson
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:gLedncWQLe7NqKrdRVn-hQ@.giganews.com...
>|||I finally settled on this test bed, which appears to work successfully:
declare @.CursorName nvarchar(4000) select @.CursorName = 'cur'
execute
(
'
declare ' + @.CursorName + ' cursor global forward_only read_only for
select name from sysobjects
'
)
declare @.Cursor cursor
declare @.Query nvarchar(4000)
select @.Query = 'set @.Cursor = ' + @.CursorName + ' open @.Cursor'
execute [dbo].[sp_executesql] @.Query, N'@.Cursor cursor output', @.Cursor = @.Cursor
output
declare @.Name sysname
fetch next from @.Cursor into @.Name
print @.Name
close @.Cursor
deallocate @.Cursor
execute('deallocate ' + @.CursorName)
Thanks again for your help, David! I was dispairing that a solution could b
e found. :-)
John Peterson
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:eKHgxlI%23DHA.2636@.TK2MSFTNGP09.phx.gbl...
> Oddly, it seems like there are some things that can't be done with the cur
sor variable.
> For example, I tried:
> open @.Cursor
> But that doesn't appear to work. Only when the OPEN is in the context of the dyna
mic
SQL
> does it seem to open the cursor for the variable.
> Additionally:
> close @.Cursor
> deallocate @.Cursor
> Don't appear to work either. If I try and re-run my code snippet, it comp
lains that the
> cursor still exists.
> Unless the issue is that there are *two* "handles" to the same cursor (the
original "By
> Name" and the variable) -- and I need to essentially close both handles before the

cursor
> will be destroyed?
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:u4t38hI%23DHA.888@.tk2msftngp13.phx.gbl...
I'm
>

Can a cursor variable be assigned to a dynamically named cursor?

(SQL Server 2000, SP3a)
(From a different thread.)
Hello, all!
I have an open global cursor that is created dynamically by stored procedure A. I'd like
to reference this cursor from stored procedure B. I know the dynamic name of the cursor,
but I know of no way to get a "handle" of this cursor so that I can use it from stored
procedure B in a cursor variable.
The [sp_describe_cursor] returns something called a cursor_handle. Can this be used
somehow to set a cursor variable?
I thought maybe I could do something like this:
declare @.CursorName nvarchar(4000) select @.CursorName = 'cur'
execute
(
'
declare ' + @.CursorName + ' cursor forward_only read_only for
select name from sysobjects
'
)
declare @.Cursor cursor
declare @.Query nvarchar(4000)
select @.Query = 'set @.Cursor = ' + @.CursorName
execute [dbo].[sp_executesql] @.Query, N'@.Cursor cursor varying output', @.Cursor = @.Cursor
output
execute('deallocate ' + @.CursorName)
But I get this error:
Server: Msg 181, Level 15, State 1, Line 1
Cannot use the OUTPUT option in a DECLARE statement.
Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable '@.Cursor'.
Which I don't fully understand. But, after some fiddling, it's clearly something with the
[sp_executesql] line. No amount of massaging will get this to work -- my guess is that
the structure of [sp_executesql] won't permit a cursor variable to be handled. :-(
Thanks for any help anyone can provide!
John PetersonCursors are usually best avoided because of their performance/resource
implications. Erland has an article on alternative methods for sharing data
between SPs:
http://www.sommarskog.se/share_data.html
For completeness, here's an amended version of your code:
...
SET @.query = 'SET @.cursor = ' + @.cursorname + ' OPEN @.cursor'
EXEC sp_executesql @.query, N' @.CURSOR CURSOR OUTPUT', @.cursor OUTPUT
...
Now reference the cursor by variable (@.cursor).
--
David Portas
SQL Server MVP
--|||David,
You're right -- I appreciate that cursors aren't wholly performant, but in my case, I'm
writing a management procedure that lends itself well to using cursors.
Thanks for the link on other techniques for sharing data. :-)
I think you solved my issue! From what I can tell, you merely removed the VARYING
keyword. From the stored procedure documentation (which I kind of assumed that
[sp_executesql] was leveraging) it seemed as if the VARYING keyword was necessary when
using a cursor variable. But, it appears not to be the case, and that was the one
combination I *didn't* try!
Thanks so much! :-)
John Peterson
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:gLedncWQLe7NqKrdRVn-hQ@.giganews.com...
> Cursors are usually best avoided because of their performance/resource
> implications. Erland has an article on alternative methods for sharing data
> between SPs:
> http://www.sommarskog.se/share_data.html
> For completeness, here's an amended version of your code:
> ...
> SET @.query = 'SET @.cursor = ' + @.cursorname + ' OPEN @.cursor'
> EXEC sp_executesql @.query, N' @.CURSOR CURSOR OUTPUT', @.cursor OUTPUT
> ...
> Now reference the cursor by variable (@.cursor).
> --
> David Portas
> SQL Server MVP
> --
>|||Oddly, it seems like there are some things that can't be done with the cursor variable.
For example, I tried:
open @.Cursor
But that doesn't appear to work. Only when the OPEN is in the context of the dynamic SQL
does it seem to open the cursor for the variable.
Additionally:
close @.Cursor
deallocate @.Cursor
Don't appear to work either. If I try and re-run my code snippet, it complains that the
cursor still exists.
Unless the issue is that there are *two* "handles" to the same cursor (the original "By
Name" and the variable) -- and I need to essentially close both handles before the cursor
will be destroyed?
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:u4t38hI%23DHA.888@.tk2msftngp13.phx.gbl...
> David,
> You're right -- I appreciate that cursors aren't wholly performant, but in my case, I'm
> writing a management procedure that lends itself well to using cursors.
> Thanks for the link on other techniques for sharing data. :-)
> I think you solved my issue! From what I can tell, you merely removed the VARYING
> keyword. From the stored procedure documentation (which I kind of assumed that
> [sp_executesql] was leveraging) it seemed as if the VARYING keyword was necessary when
> using a cursor variable. But, it appears not to be the case, and that was the one
> combination I *didn't* try!
> Thanks so much! :-)
> John Peterson
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:gLedncWQLe7NqKrdRVn-hQ@.giganews.com...
> > Cursors are usually best avoided because of their performance/resource
> > implications. Erland has an article on alternative methods for sharing data
> > between SPs:
> >
> > http://www.sommarskog.se/share_data.html
> >
> > For completeness, here's an amended version of your code:
> > ...
> > SET @.query = 'SET @.cursor = ' + @.cursorname + ' OPEN @.cursor'
> > EXEC sp_executesql @.query, N' @.CURSOR CURSOR OUTPUT', @.cursor OUTPUT
> > ...
> >
> > Now reference the cursor by variable (@.cursor).
> >
> > --
> > David Portas
> > SQL Server MVP
> > --
> >
> >
>|||I finally settled on this test bed, which appears to work successfully:
declare @.CursorName nvarchar(4000) select @.CursorName = 'cur'
execute
(
'
declare ' + @.CursorName + ' cursor global forward_only read_only for
select name from sysobjects
'
)
declare @.Cursor cursor
declare @.Query nvarchar(4000)
select @.Query = 'set @.Cursor = ' + @.CursorName + ' open @.Cursor'
execute [dbo].[sp_executesql] @.Query, N'@.Cursor cursor output', @.Cursor = @.Cursor output
declare @.Name sysname
fetch next from @.Cursor into @.Name
print @.Name
close @.Cursor
deallocate @.Cursor
execute('deallocate ' + @.CursorName)
Thanks again for your help, David! I was dispairing that a solution could be found. :-)
John Peterson
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:eKHgxlI%23DHA.2636@.TK2MSFTNGP09.phx.gbl...
> Oddly, it seems like there are some things that can't be done with the cursor variable.
> For example, I tried:
> open @.Cursor
> But that doesn't appear to work. Only when the OPEN is in the context of the dynamic
SQL
> does it seem to open the cursor for the variable.
> Additionally:
> close @.Cursor
> deallocate @.Cursor
> Don't appear to work either. If I try and re-run my code snippet, it complains that the
> cursor still exists.
> Unless the issue is that there are *two* "handles" to the same cursor (the original "By
> Name" and the variable) -- and I need to essentially close both handles before the
cursor
> will be destroyed?
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:u4t38hI%23DHA.888@.tk2msftngp13.phx.gbl...
> > David,
> >
> > You're right -- I appreciate that cursors aren't wholly performant, but in my case,
I'm
> > writing a management procedure that lends itself well to using cursors.
> >
> > Thanks for the link on other techniques for sharing data. :-)
> >
> > I think you solved my issue! From what I can tell, you merely removed the VARYING
> > keyword. From the stored procedure documentation (which I kind of assumed that
> > [sp_executesql] was leveraging) it seemed as if the VARYING keyword was necessary when
> > using a cursor variable. But, it appears not to be the case, and that was the one
> > combination I *didn't* try!
> >
> > Thanks so much! :-)
> >
> > John Peterson
> >
> >
> > "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> > news:gLedncWQLe7NqKrdRVn-hQ@.giganews.com...
> > > Cursors are usually best avoided because of their performance/resource
> > > implications. Erland has an article on alternative methods for sharing data
> > > between SPs:
> > >
> > > http://www.sommarskog.se/share_data.html
> > >
> > > For completeness, here's an amended version of your code:
> > > ...
> > > SET @.query = 'SET @.cursor = ' + @.cursorname + ' OPEN @.cursor'
> > > EXEC sp_executesql @.query, N' @.CURSOR CURSOR OUTPUT', @.cursor OUTPUT
> > > ...
> > >
> > > Now reference the cursor by variable (@.cursor).
> > >
> > > --
> > > David Portas
> > > SQL Server MVP
> > > --
> > >
> > >
> >
> >
>

calling use @dbname in a cursor

Folks,
I am trying to use a cursor to run space check on each db on the server
OPEN cur_DBs
FETCH NEXT FROM cur_DBs INTO @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
--************************************************** ************************************************** ******************
use @.DBName
won't let me do the "use @.dbname" to switch to the db I want to use.
Any ideas?
Thanks,
MPM
Try:
sp_MSforeachdb 'EXEC [?].dbo.sp_spaceused'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"MANCPOLYMAN" <MANCPOLYMAN@.discussions.microsoft.com> wrote in message
news:8CC1C001-4D22-43C3-A0FF-C32F13EBB2AC@.microsoft.com...
> Folks,
> I am trying to use a cursor to run space check on each db on the server
> OPEN cur_DBs
> FETCH NEXT FROM cur_DBs INTO @.DBName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> --************************************************** ************************************************** ******************
> use @.DBName
> won't let me do the "use @.dbname" to switch to the db I want to use.
> Any ideas?
> Thanks,
> MPM
|||Tibor,
I behold the beauty of the internet. Exactly what I am looking for.
Thanks a lot,
Brian
"Tibor Karaszi" wrote:

> Try:
> sp_MSforeachdb 'EXEC [?].dbo.sp_spaceused'
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "MANCPOLYMAN" <MANCPOLYMAN@.discussions.microsoft.com> wrote in message
> news:8CC1C001-4D22-43C3-A0FF-C32F13EBB2AC@.microsoft.com...
>
>

calling use @dbname in a cursor

Folks,
I am trying to use a cursor to run space check on each db on the server
OPEN cur_DBs
FETCH NEXT FROM cur_DBs INTO @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGI
--**********************************************************************************************************************
use @.DBName
won't let me do the "use @.dbname" to switch to the db I want to use.
Any ideas?
Thanks,
MPMTry:
sp_MSforeachdb 'EXEC [?].dbo.sp_spaceused'
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"MANCPOLYMAN" <MANCPOLYMAN@.discussions.microsoft.com> wrote in message
news:8CC1C001-4D22-43C3-A0FF-C32F13EBB2AC@.microsoft.com...
> Folks,
> I am trying to use a cursor to run space check on each db on the server
> OPEN cur_DBs
> FETCH NEXT FROM cur_DBs INTO @.DBName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> --**********************************************************************************************************************
> use @.DBName
> won't let me do the "use @.dbname" to switch to the db I want to use.
> Any ideas?
> Thanks,
> MPM|||Tibor,
I behold the beauty of the internet. Exactly what I am looking for.
Thanks a lot,
Brian
"Tibor Karaszi" wrote:
> Try:
> sp_MSforeachdb 'EXEC [?].dbo.sp_spaceused'
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "MANCPOLYMAN" <MANCPOLYMAN@.discussions.microsoft.com> wrote in message
> news:8CC1C001-4D22-43C3-A0FF-C32F13EBB2AC@.microsoft.com...
> > Folks,
> >
> > I am trying to use a cursor to run space check on each db on the server
> >
> > OPEN cur_DBs
> > FETCH NEXT FROM cur_DBs INTO @.DBName
> > WHILE @.@.FETCH_STATUS = 0
> > BEGIN
> > --**********************************************************************************************************************
> >
> > use @.DBName
> >
> > won't let me do the "use @.dbname" to switch to the db I want to use.
> >
> > Any ideas?
> >
> > Thanks,
> > MPM
>
>

calling use @dbname in a cursor

Folks,
I am trying to use a cursor to run space check on each db on the server
OPEN cur_DBs
FETCH NEXT FROM cur_DBs INTO @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- ****************************************
**********************************
****************************************
****
use @.DBName
won't let me do the "use @.dbname" to switch to the db I want to use.
Any ideas?
Thanks,
MPMTry:
sp_MSforeachdb 'EXEC [?].dbo.sp_spaceused'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"MANCPOLYMAN" <MANCPOLYMAN@.discussions.microsoft.com> wrote in message
news:8CC1C001-4D22-43C3-A0FF-C32F13EBB2AC@.microsoft.com...
> Folks,
> I am trying to use a cursor to run space check on each db on the server
> OPEN cur_DBs
> FETCH NEXT FROM cur_DBs INTO @.DBName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> -- ****************************************
********************************
****************************************
******
> use @.DBName
> won't let me do the "use @.dbname" to switch to the db I want to use.
> Any ideas?
> Thanks,
> MPM|||Tibor,
I behold the beauty of the internet. Exactly what I am looking for.
Thanks a lot,
Brian
"Tibor Karaszi" wrote:

> Try:
> sp_MSforeachdb 'EXEC [?].dbo.sp_spaceused'
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "MANCPOLYMAN" <MANCPOLYMAN@.discussions.microsoft.com> wrote in message
> news:8CC1C001-4D22-43C3-A0FF-C32F13EBB2AC@.microsoft.com...
>
>