January 10, 2003 at 12:31 am
Hi,
We have a situation wherein we query across database and the database name is passed as a parameter to the SP.
The issue is the building of the cursor statement. The query is atored in a variable and that variable is passed to the cursor. It reports an error.
Example,
declare @STR='dbname.dbo.tablename'
(where dbname is an in paramater)
How to substitute this variable when declaring the cursor and make it work??
Pls help.
Thks in advance
January 10, 2003 at 3:34 am
All the code has to be wrapped in an Execute or sp_executesql string input. You cannot substitute a table with a variable.
January 10, 2003 at 3:47 am
Antares686 is right, you have to use
set @sql = 'DECLARE curs CURSOR FOR select col1,col2 from ' + @table
exec sp_executesql @sql
OPEN curs
FETCH ....
CLOSE curs
DEALLOCATE curs
Far away is close at hand in the images of elsewhere.
Anon.
January 13, 2003 at 2:10 pm
you can also use the execute command to create a global cursor that you can then run locally.
January 13, 2003 at 6:57 pm
Another way is to not use a cursor but instead create a temp table with an IDENTITY column and use a while loop using the identity column to get the record you want to work with. You will still need to use sp_Execute to do the insert into the temp table though.
Gary Johnson
DBA
Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
January 17, 2003 at 9:35 pm
quote:
All the code has to be wrapped in an Execute or sp_executesql string input. You cannot substitute a table with a variable.Thanks a lot buddy!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply