input paramater in a stored proceduregreenspun.com : LUSENET : SQL Server Database Administration : One Thread
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 + "'")
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
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,
-- Anonymous, September 08, 2000