May 7, 2003 at 9:02 am
Hi there,
I am trying to create a stored procedure in which I would like it returns a cursor. I could make it work for returning a cursor. However, I only wanted to take one or two of the returned columns from the cursor. Is SQL Server 2000 allows to do that? The funny thing is that I could only Fetch the entire cursor, not INTO local variables, such as 'FETCH NEXT FROM @curReturn --INTO @tblIndexName, @tblIndexKeys', so that I can play around with the returned values. I must commented out the INTO clause in order to make the fetch statement work. Is there anyone could help?
AC
May 7, 2003 at 9:59 am
Each variable in the list, from left to right, is associated with the corresponding column in the cursor result set. The number of variables must match the number of columns in the cursor select list.
May 7, 2003 at 10:05 am
For this example, I only have 2 columns in my returned cursor. The fetch statement is matching to the select list but I only wanted to take the value from the @tblIndexKeys. I tried to @curReturn.IndexKeys and @curReturn(IndexKeys) --> this is the select column that in my cursor select statement. I know it is a self invention and of course they don't work. Can you please provide me a simple example for how could I take partial columns from the returned cursor?
May 7, 2003 at 10:11 am
Example from BOL.
B. Use FETCH to store values in variables
This example is similar to the last example, except the output of the FETCH statements is stored in local variables rather than being returned directly to the client. The PRINT statement combines the variables into a single string and returns them to the client.
USE pubs
GO
-- Declare the variables to store the values returned by FETCH.
DECLARE @au_lname varchar(40), @au_fname varchar(20)
DECLARE authors_cursor CURSOR FOR
SELECT au_lname, au_fname FROM authors
WHERE au_lname LIKE 'B%'
ORDER BY au_lname, au_fname
OPEN authors_cursor
-- Perform the first fetch and store the values in variables.
-- Note: The variables are in the same order as the columns
-- in the SELECT statement.
FETCH NEXT FROM authors_cursor
INTO @au_lname, @au_fname
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- Concatenate and display the current values in the variables.
PRINT 'Author: ' + @au_fname + ' ' + @au_lname
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM authors_cursor
INTO @au_lname, @au_fname
END
CLOSE authors_cursor
DEALLOCATE authors_cursor
GO
Author: Abraham Bennet
Author: Reginald Blotchet-Halls
May 7, 2003 at 10:16 am
May be my messages are not clear enough. What I am trying to do is a stored procedure (sp) returns a cursor, and the calling sp gets the returned cursor and plays around with the recordset values from the returned cursor (like in Oracle). Is that make sense?
Thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply