March 6, 2003 at 5:13 pm
All,
I need some help with a problem I ran into when creating a stored procedure. I'm trying to declare a cursor thats using a select statement that contains a variable.
declare Update_IndexID cursor for
Select @IndexColumn from ##CopyCaseIDtbl
Now earlier I had a similar problem with running a select statement containing a variable name and solved it by placing the query in a variable and then running the variable like this:
SET @sQuery = 'Select ' +@IndexColumn+' from ##CopyCaseIDtbl'
exec (@squery)
Is there a similar way of fooling the cursor into doing this? Can you set the entire declare statement as a varible and then execute it?
Any help on this would be appreciated!
March 7, 2003 at 1:44 am
You would either need to set the entire declare cursor and the entire code that then uses the cursor inside the query-variable and execute it, or you would have to declare a global cursor and have the entire declare statement inside the query.
The real question is what are you trying to accomplish, and why are you using a cursor?
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
March 7, 2003 at 2:29 am
declare @sql nvarchar(200)
set @sql = 'declare Update_IndexID cursor for Select '+@IndexColumn+' from ##CopyCaseIDtbl'
exec sp_executesql @sql
OPEN Update_IndexID
FETCH NEXT FROM Update_IndexID
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Update_IndexID
END
CLOSE Update_IndexID
DEALLOCATE Update_IndexID
Far away is close at hand in the images of elsewhere.
Anon.
March 7, 2003 at 8:40 am
David, minor code change to get your code to work. Without declaring that cursor global the scope of it would be limited to the dynamic sql. Chris was on the mark, you either have to do the cursor COMPLETELY in dynamic sql, or declare the cursor global, neither solution is attractive to me. Try pasting the below code into QA, it should run just with F5, and no changes neccessary.
USE PUBS
GO
DECLARE @sql nvarchar(200),
@IndexColumn varchar(40),
@IndexValue varchar(40)
SET @IndexColumn = 'au_id'
SET @sql = 'DECLARE hCUpdate_IndexID CURSOR GLOBAL FOR Select ' + @IndexColumn + ' from authors'
EXEC sp_executesql @sql
OPEN hCUpdate_IndexID
FETCH NEXT FROM hCUpdate_IndexID INTO @IndexValue
PRINT @IndexValue
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM hCUpdate_IndexID INTO @IndexValue
PRINT @IndexValue
END
CLOSE hCUpdate_IndexID
DEALLOCATE hCUpdate_IndexID
Tim C.
//Will write code for food
Tim C //Will code for food
March 7, 2003 at 8:47 am
Interesting Tim, my code worked OK for me in QA. I'm running SQL7 SP4.
nb Your code runs OK in QA with or without the GLOBAL option.
Far away is close at hand in the images of elsewhere.
Anon.
March 7, 2003 at 8:50 am
All,
Thanks for the help..I realized last nite as I was posting this that I had never tried setting the entire declare statement in a variable and executing it. I tried that right afterwards and it worked. Somebody asked why I was doing this. I'm writing a SP that takes a 'name', finds its case history, then duplicates that case history with an entirely new 'name'. This history is passed through many different tables and instead of creating an update for every table I am using a cursor for the table names and a cursor for the known index columns and updating them one table at a time through cursors.
Thanx again for all the help!!
March 7, 2003 at 9:21 am
Hmmmm, you right David, I had tried doing this exact thing before withought using a global cursor, and it did not work, or I could not get it to work at the time. So I did not even try your code out before I refuted it. I apologize for a correction where none was needed. I wonder why I could not get it to work before. I even tried using EXEC (@sql) instead, and it worked.
Tim C.
//Will write code for food
Tim C //Will code for food
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply