The EXEC seems to spin off another process and the cursor doesnt like that. Heres what I have tried:

declare @pTableName varchar (10) declare @pOld varchar(6) set @pTableName = 'joshtemp' set @pOld = 'Josh'

exec("Select ID1 from " + @pTableName + " where id1 = '" + @pOld + "'")

The above code works. But it's a different story when I try to set a cursor to it:

CREATE procedure JoshTemp2 @pTableName varchar (10), @pNew varchar(6), @pOld varchar(6)

as declare @ID1 as varchar (6) declare @ID2 as varchar (6)

declare TmpCur cursor for exec("Select ID1 from " + @pTableName + " where id1 = '" + @pOld + "'")

open TmpCur

fetch next from TmpCur into @ID1 for update

while (@@fetch_status <> -1) begin

set @ID1 = @pNew fetch next from TmpCur into @Id1 end

Now when you click check syntax you get an "Incorrect syntax near EXEC" and "Incorrect syntax near "select ID1 From "" and Incorrect syntax near keyword 'For' I hope that it's just me doing something wrong, and not SQL server's ability. Thanks for being patient.

-- Anonymous, September 07, 2000

Answers

Josh,

I meant to suggest that you put the whole shebang in the string that is executed by the EXEC. (I apologize for calling it a statement. It is several statements) This string will include the cursor declaration, the open, and the fetch. And also the close and the deallocation. ;-)

Hope this helps,

Eric

-- Anonymous, September 08, 2000