June 12, 2003 at 2:22 am
I am building a dynamic sql statement depending on data in a table.
Afterwards I want to declare a cursor like the following
DECLARE dynstmnt_cursor CURSOR FOR @dynamicsql
which obviously is not allowed because the compiler does not accept the syntax.
The variable @dynamicsql will contain a normal 'select * from table' statement.
Is there a way do this.
Would appreciate some help
June 12, 2003 at 2:25 am
You cannot specific the sql statement as a variable for the cursor.
The easiest was around this is to include the whole cursor operation into your dynamic sql statement.
June 12, 2003 at 2:32 am
Thanks for the quick response.
I know that I can not use this kind of syntax but is there any other way of doing it without including the whole cursor declaration and the processing afterwards in the dynamic statement.
Because the way I understand it the fetch will also have to be included in the dynamic statement and that is going to make things very difficult.
Regards
June 12, 2003 at 2:35 am
Your understanding is correct, because the dynamic sql will be executing in a differenc scope to your main procedure you will have to include the WHOLE cursor operation (declare, open, fetch, close, deallocate) within your dynamic code.
June 12, 2003 at 2:40 am
Just has a thought......
In your code prior to declaring the cursor, create a temporary table and populate the temporary table with the data from the dynamic statement. Then you could hard code the reference the temporary table in your declaration of the cursor. I think this should give you the flexibilty you require.
Hope it helps.
June 12, 2003 at 2:44 am
Yes, I think the temporary table is the best solution
Thanks for your help.
Appreciate it
June 12, 2003 at 6:56 am
Actually you can, as long as it is in the same session/proc, like this
declare @sql nvarchar(1000)
set @sql = 'DECLARE curs CURSOR FOR select col1,col2 from table'
exec sp_executesql @sql
declare @col1 int,@col2 int
OPEN curs
FETCH NEXT FROM curs INTO @col1,@col2
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @col1,@col2
FETCH NEXT FROM curs INTO @col1,@col2
END
CLOSE curs
DEALLOCATE curs
Depending on how much data what you want to do with it, temp table is probably the best.
Edited by - davidburrows on 06/12/2003 06:56:13 AM
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply