how to set sp_executesql result sets to a cursor

how to set sp_executesql result sets to a cursor

Post by Michae » Thu, 05 Feb 2004 05:25:10



Hi,

Can I set the "return result sets" to a cursor when running "sp_executesql
"sql_select_statement" ?
The "sql_select_statement" is dynamic generated at run time.
e.g.


How can I make this works?
Thanks.

Rgds

 
 
 

how to set sp_executesql result sets to a cursor

Post by Anith Se » Thu, 05 Feb 2004 06:43:14


One approach is to use a stored procedure with the Dynamic SQL like:


AS
CREATE TABLE #t(c1 INT NOT NULL)
INSERT #t EXEC('SELECT OrderId FROM Northwind..Orders')

SELECT c1 FROM #t
GO

Now you can do:


--
- Anith
( Please reply to newsgroups only )

 
 
 

1. Result Sets vs copying result sets into memory.

Does anyone see a problem with copying a result set into a collection
for display vs looping through the result set for display, so I can
return the connection back to the database little faster? I am using
connection pooling, so there is no connection overhead in creating a
new query.

The only performance issue I can see that each result set will take up
memory, but after it's displayed, that memory will be available for
something else, right?  I'm in a situation where up to 500 people can
hit a webpage for data at the same time.

I'm starting to run out of database connections on the web application
and a bigger database isn't an option for me. I can (and do) cache
persistant non-changing data, but have been told that calls for
changing and updated data must always come straight from the database.

Some of the result sets are over 10000 records long and there's no way
to split them. Through sql, I am able to return only rows X through
X+20, so I only return what I am going to show.

Any POSITIVE feedback would be appreciated.

Thanks in advance,

-- ABS

2. Weird Lockup - D3 - AIX

3. How to use sp_ExecuteSQL's result set...

4. Challenge...

5. How to test for sp_executesql result sets

6. Informix OWS Memory Leak

7. sp_executesql result set

8. Newbie question -- Max

9. Trying to use set-based result set instead of looping

10. Help to set local variable equal to value of field in result set

11. Setting Result-set Range

12. Set Criteria is not affecting resulting set

13. Set Precedence Against Multiple Result Sets?