Controlling the cursor position

Let's say I run a query that returns a 100k records. Is there a way to
control where the cursor is in the result set. For example, can I get it
to jump to row 50k and return the next 500 rows?Taken from Transact SQL help:
-- Fetch the row that is two rows prior to the current row.
FETCH RELATIVE -2 FROM authors_cursor
Hope that helps
"preston" wrote:

> Let's say I run a query that returns a 100k records. Is there a way to
> control where the cursor is in the result set. For example, can I get it
> to jump to row 50k and return the next 500 rows?
>|||Hi, preston
See: http://www.aspfaq.com/show.asp?id=2120
Razvan

control yank in 2005 Query Designer

How can I assign ctrl+y to remove the line the cursor is on when designing a query in management studio like it was defaulted on in 2000? Muchos gracias.I hate sql 2005 it can suck a duck for all i give a damn please let there be a way to restore my databases back to 2000 how do ctrl+f4 for search this is worse than switching from mssql to oracle on unix why is no backward compatability maintained did apple by these perverts out or something? peace out my ninjas you have been a great lot of help may you eternally be blessed with your just rewards

Context Change and Cursor

I have T-SQL code that is used to run utilities against a particular list of databases that are stored in a table. I have a cursor that gets the list of DBs from the sysdatabases table based on entries in a table in a specific database. The list of databases will vary from server to server, so I need to have it use variables rather than code the db names into the script.

declare @.DatabaseId char(8)

declare DatabaseLoop cursor for
select name from master..sysdatabases where name in
(select <column name> from <db name..table> )

open DatabaseLoop
fetch next from DatabaseLoop into @.DatabaseId
while (@.@.fetch_status <> -1)
begin

...

So far, the context has not mattered, for example - backup @.DatabaseID works fine within the cursor since the context doesn't need to change. The problem is that I want to run particular scripts that require the context to be changed to the database. But, of course, USE @.DatabaseID does not work inside the cursor.

So, is there another way to be able to run my script against multiple variables other than using a cursor?

I am not necessarily looking for you to write my code, but if someone could point me in the right direction, I would appreciate it.

I would suggest that you use dynamic SQL for this and you can use a use in there.

declare @.query varchar(8000) -- I am assuming 2000 since you

--used sysdatabases

set @.query = 'use ' + @.database + ' select * from sysobjects'

exec (@.query)

I know this works in 2005, and am pretty sure it worked in 2000

Edit: Sorry, sent the message from my phone and it did a terrible job with the formatting

consuming result sets from dynamic queries within a cursor

Hi SQLers
I have the flowing scenario. I have a cursor that amongst other things
returns me the name of a stored procedure that needs to run and a parameter
to pass to it. Depending on whether the stored proc returns a result I then
decide what to do with the other data I get from the cursor. But I want to
suppress the result sets coming from the inner stored proc calls.
I basically want something like this in my cursor loop
insert into #tmp exec ( @.StoredProcedureName + ' ' + @.messageID )
if exists (select 1 from #tmp)
-- do something with cursor data
else
-- do something else with cursor data
-- where @.StoredProcedureName and @.messageID are data from the cursor
but I get an error saying invalid object #tmp
I cannot declare #tmp as a table variable as unfortunately the stored
proceedures called are different for different iterations of the cursor, tha
t
is @.StoredProcedureName changes and the result set from the different
procedures is different. I cannot make them the same as each of these proc
does a different job and they are called by different callers and so cannot
be changed.
and I dont want to say
exec ( @.StoredProcedureName + ' ' + @.messageID )
if (@.@.ROWCOUNT > 0)
-- do something
else
-- do something else
as the result set of the exec is then returned to the caller.
If anyone has any ideas I would be very greatful. I know some ways to solve
this in C# .NET but I was just wondering if there is a SQL solution. I expec
t
there is not but thought I would ask those that are likely to know.
Many thanks
DerekI don't understand why you would want to execute a stored proc dynamically
based on a cursor. TSQL does have IF statements for flow control but what
you are describing is a very procedural solution. Are you sure there isn't a
set-based SQL alternative to all this?
Read this article on how to do dynamic SQL (and why NOT to do it):
http://www.sommarskog.se/dynamic_sql.html
This one explains how you can process result sets from an SP:
http://www.sommarskog.se/share_data.html
David Portas
SQL Server MVP
--|||Hi David,
Thanks for your time.
Basically my cursor is finding all entries in a MessageReceipients table
that have NULL as their SentDateTime and once armed with the MessageID and
@.MessageType for these intended recipients, I need to call the appropriate
stored procedures (the actual stored proc will depend on the MessageType) to
see if these receipients can still have messages sent to them.
For example it is possible that a message receipient is in the
MessageRecipient table and that the mail system originally failed to send th
e
message but by the time we come to do this check (to resend the message) the
intended recipient my no longer want or be able to receive messages. This
fact can only be determined by calling the @.StoredProcedureName (in my
original code sample) and the reason this is dynamic is that the procedure
that needs to be checked to determin if a message can still be sent is
different for different message types.
I know I can solve this problem in my C# code and perhaps this is the place
to do it. For example I could just return all receipients that have got
potentially outstanding messages. And in the C# check the @.MessageType and
@.MessageID and then call the @.StoredProcedureName with @.MessageID to deterim
whether it is really appropriate to send the message to the receipient.
I was just hoping to not have to do this step in the C# application if I
could avoid it.
I also realise that re writing the various @.StoredProcedureName SPs would
make life easier but unfortunately they are used by other parts of the app
and so I cannot change them. So I would have to esentially duplicate there
functionality with the exception of how they return their results and that
would not be good from a maintenance perspective.
Having said that for now all I am doing in the C# application is looking at
the last result set returned from my SP as this is the result set that I
want. But this is only ok as the results sets are quite small and so I do no
t
need to worry to much about returning unwanted data to the C# application.
Hope that make things a little clearer
Many thanks
Derek
"David Portas" wrote:

> I don't understand why you would want to execute a stored proc dynamically
> based on a cursor. TSQL does have IF statements for flow control but what
> you are describing is a very procedural solution. Are you sure there isn't
a
> set-based SQL alternative to all this?
> Read this article on how to do dynamic SQL (and why NOT to do it):
> http://www.sommarskog.se/dynamic_sql.html
> This one explains how you can process result sets from an SP:
> http://www.sommarskog.se/share_data.html
> --
> David Portas
> SQL Server MVP
> --
>
>