Creating Cursor from Stored Procedure

[Lucky]

Hi guys!
i want to create one cursor in the t-sql. the problem is i want to use
stored procedure instead of select command in cursor.

can anyone tell me how can i use stored procedure's o/p to create
cursor?

i'm using sql 2000 and .net 2.0

thanks,

Lucky

[Stu]

First, try to rewrite your app so you don't use cursors.

Second, if you must use a cursor, you can create a temp table to hold
the output of your stored procedure, and then build a cursor from that;
e.g.:

CREATE TABLE #splat (columnlist)

INSERT INTO #splat
exec myproc

DROP TABLE #splat

Stu

Lucky wrote:

Hi guys!
i want to create one cursor in the t-sql. the problem is i want to use
stored procedure instead of select command in cursor.

can anyone tell me how can i use stored procedure's o/p to create
cursor?

i'm using sql 2000 and .net 2.0

thanks,

Lucky

[Madhivanan]

Post your exact requirement. There can be better method of what you are
trying to do now

Madhivanan
Lucky wrote:

Hi guys!
i want to create one cursor in the t-sql. the problem is i want to use
stored procedure instead of select command in cursor.

can anyone tell me how can i use stored procedure's o/p to create
cursor?

i'm using sql 2000 and .net 2.0

thanks,

Lucky

[Jason Kester]

Stu wrote:

First, try to rewrite your app so you don't use cursors.

Second, Seriously. Try to rewrite your app so you don't use cursors.

You might also consider dropping the guts of your stored procedure into
a User Defined Function that returns a table. Then you can use that
UDF for both the Stored Procedure and your sketchy thing that uses
Cursors.

Good luck!

Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/

---
Get your own Travel Blog, with itinerary maps and photos!
http://www.blogabond.com/

[Lucky]

Hi ,
Here i'm pasting the sql code that i want to run. the code is ment to
fetch datbase list and update each database with some specific business
logic.

DECLARE authors_cursor CURSOR FOR
SELECT sp_databases

OPEN authors_cursor

FETCH NEXT FROM authors_cursor
INTO @Database_name, @database_size, @Remarks

WHILE @@FETCH_STATUS = 0
BEGIN

print 'database : ' + @Database_name
print 'some business logic'
FETCH NEXT FROM authors_cursor
INTO @au_id, @au_fname, @au_lname
END

CLOSE authors_cursor
DEALLOCATE authors_cursor

NOTE:

please notice the use of procedure to get list of the database in the
select statement of the DECLARING CURSOR. i want to use stored
procedure's o/p to iterate through the rows returned by the procedure.

Please let me know if you know how to do this.

thanks

Madhivanan wrote:

Post your exact requirement. There can be better method of what you are
trying to do now

Madhivanan
Lucky wrote:

Hi guys!
i want to create one cursor in the t-sql. the problem is i want to use
stored procedure instead of select command in cursor.

can anyone tell me how can i use stored procedure's o/p to create
cursor?

i'm using sql 2000 and .net 2.0

thanks,

Lucky

[Erland Sommarskog]

Lucky (tu************ @gmail.com) writes:

Here i'm pasting the sql code that i want to run. the code is ment to
fetch datbase list and update each database with some specific business
logic.

DECLARE authors_cursor CURSOR FOR
SELECT sp_databases
There is no table?
OPEN authors_cursor

FETCH NEXT FROM authors_cursor
INTO @Database_name, @database_size, @Remarks

WHILE @@FETCH_STATUS = 0
BEGIN

print 'database : ' + @Database_name
print 'some business logic'
FETCH NEXT FROM authors_cursor
INTO @au_id, @au_fname, @au_lname
END

CLOSE authors_cursor
DEALLOCATE authors_cursor

NOTE:

please notice the use of procedure to get list of the database in the
select statement of the DECLARING CURSOR. i want to use stored
procedure's o/p to iterate through the rows returned by the procedure.

What does "o/p" mean?

It would be interesting to know what "some business logic" contains.

It's possible that you could use sp_MSforeachdb:

EXEC sp_MSforeachdb N'SELECT db = ''?'', COUNT(*) FROM [?]..sysobjects'

This procedure is undocumented and not supported from Microsoft, so
you would have to look into the source code for the gory details on
how it works. But basically it iterates over all databases, and
run as the SQL statement once for each database. ? works as placeholder
for the database name.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Lucky]

yeah there is no table. its just a database list that i need. and the
business logic is very very big to paste here. i need some different
tables in different DB and the idea of using the foreachloop is
interesting but i dont know wether it will work with more than 250
lines of PL SQL code.

the o/p mean OUTPUT.

let me know if you want to know anything else.

Erland Sommarskog wrote:

Lucky (tu************ @gmail.com) writes:

Here i'm pasting the sql code that i want to run. the code is ment to
fetch datbase list and update each database with some specific business
logic.

DECLARE authors_cursor CURSOR FOR
SELECT sp_databases

There is no table?

OPEN authors_cursor

FETCH NEXT FROM authors_cursor
INTO @Database_name, @database_size, @Remarks

WHILE @@FETCH_STATUS = 0
BEGIN

print 'database : ' + @Database_name
print 'some business logic'
FETCH NEXT FROM authors_cursor
INTO @au_id, @au_fname, @au_lname
END

CLOSE authors_cursor
DEALLOCATE authors_cursor

NOTE:

please notice the use of procedure to get list of the database in the
select statement of the DECLARING CURSOR. i want to use stored
procedure's o/p to iterate through the rows returned by the procedure.

What does "o/p" mean?

It would be interesting to know what "some business logic" contains.

It's possible that you could use sp_MSforeachdb:

EXEC sp_MSforeachdb N'SELECT db = ''?'', COUNT(*) FROM [?]..sysobjects'

This procedure is undocumented and not supported from Microsoft, so
you would have to look into the source code for the gory details on
how it works. But basically it iterates over all databases, and
run as the SQL statement once for each database. ? works as placeholder
for the database name.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

Lucky (tu************ @gmail.com) writes:

yeah there is no table. its just a database list that i need.
So how does the actual cursor declaration look like? The code you
posted was incorrect, as it referred to a non-existing column. It's
very difficult to assist when I don't really know what you are trying
to do.
and the business logic is very very big to paste here. i need some
different tables in different DB and the idea of using the foreachloop
is interesting but i dont know wether it will work with more than 250
lines of PL SQL code.

PL/SQL? What are you using? MS SQL Server or Oracle?

To me it sounds very funny of wanting to run 250 lines of business logic
in multiple databases. I can envision situations where this may be
necessary, but I can also see this as a result of a poor design.

If you explained what your are actually trying to achieve in business
terms, it may be easier to suggest a good solution.

For a general discussion on multiple databases, this section in my
article on dynamic SQL may give some ideas:
http://www.sommarskog.se/dynamic_sql.html#Dyn_DB.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Lucky]

I'm quite dissappointed with your Questions. what i wanted to do so far
is to use output of the stored procedure in the select statement of the
Declaring CURSOR. but you have diverted the conversation on the
different track.

-- first i clearly said in my first post that i'm using MS SQL Server
2000 and .NET 2.0

-- for your convinience i gave you expamle of declaring cursor that i
had copied form the ms sql help but instead of understanding the
problem you complained about the syntaxt though that example was for to
understand the problem but you missed the target.

-- PL SQL is of course in Oracle to write some custom business logic.
the same way you can do in SQL Server the name used in here is T-SQL.
it shouldn't be hard for you to understand.

-- As far as i know, nobody ever asked me what kind of business logic i
want to use. we always disscus problems here and asked for the
solution.

-- what kind of businees logic i'm using and why i'm using and what
should be the size of the logic. these all depends on the requirements
and the scererios. i didn't ask your opinion on that.

We have streached the conversation to far and i dont want to continue
it further more.

thanks for nothing. and by the way i found what i was looking for.

Lucky

Erland Sommarskog wrote:

Lucky (tu************ @gmail.com) writes:

yeah there is no table. its just a database list that i need.

So how does the actual cursor declaration look like? The code you
posted was incorrect, as it referred to a non-existing column. It's
very difficult to assist when I don't really know what you are trying
to do.

and the business logic is very very big to paste here. i need some
different tables in different DB and the idea of using the foreachloop
is interesting but i dont know wether it will work with more than 250
lines of PL SQL code.

PL/SQL? What are you using? MS SQL Server or Oracle?

To me it sounds very funny of wanting to run 250 lines of business logic
in multiple databases. I can envision situations where this may be
necessary, but I can also see this as a result of a poor design.

If you explained what your are actually trying to achieve in business
terms, it may be easier to suggest a good solution.

For a general discussion on multiple databases, this section in my
article on dynamic SQL may give some ideas:
http://www.sommarskog.se/dynamic_sql.html#Dyn_DB.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

Lucky (tu************ @gmail.com) writes:

I'm quite dissappointed with your Questions. what i wanted to do so far
is to use output of the stored procedure in the select statement of the
Declaring CURSOR. but you have diverted the conversation on the
different track.
Yes, I want to help you to solve the real problem.

I've been following technical newsgroups on Usenet for many years, and I
early made the observation that when people asked "funny questions" was
that they were trying to get from A to B, but instead they were asking
of how to get from C ro D, because they the way from A to C and from
D to B and now they were standing at deep ravine and not being able to
cross. While there in fact there was a straight motorway from A to B,
which was easy to point to, once the real problem had been uncovered.
-- for your convinience i gave you expamle of declaring cursor that i
had copied form the ms sql help but instead of understanding the
problem you complained about the syntaxt though that example was for to
understand the problem but you missed the target.
I'm afraid that those are the rules. If you cannot make yourself clear
what you are asking for, then you will not get very good answers. I'm
sorry, but while I'm good at SQL, I am not good reading other people's
thoughts.
-- PL SQL is of course in Oracle to write some custom business logic.
the same way you can do in SQL Server the name used in here is T-SQL.
it shouldn't be hard for you to understand.

It happens frequently enough that people who use Oracle, MySQL or some
other engine post to this newsgroup, that I felt obliged to rule out this
possibility.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx