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
No comments:
Post a Comment