create cursor from non static statement 2

[NullTerminator]

I have the following
set @crsTablesToProcess = cursor for select Table_Name
from t_TableList_SalesNet2
where partition <>'no'

I would like to have the following
set @crsTablesToProcess = cursor for select Table_Name
from @TableNamePassedAsArgument
where partition <>'no'

Is there a way to create a cursor based on non static statement

Thanks for your feedback
\0

 

[TheJon]

I have tested the use of the exec command for cursor declaration, and it works. Try something like this:

exec ('set @crsTablesToProcess = cursor for select Table_Name from ' + @TableNamePassedAsArgument + 'where partition <>''no''')

[tt]|_ |_ _ | _ _
|, | )(/, |(_)| )
'[/tt]

 

[NullTerminator]

The exec doesn't seem to see the declaration of the cursor. Do I have to make it global somehow

declare @crsTablesToProcess as cursor

exec('set @crsTablesToProcess = cursor for select Table_Name '
+ ' from ' + @Table_TableNames +
' where partition <> ''no''')

Server: Msg 137, Level 15, State 3, Line 1
Must declare the variable '@crsTablesToProcess'.
Server: Msg 16950, Level 16, State 2, Procedure usp_DeleteActiveFromArchive, Line 50
The variable '@crsTablesToProcess' does not currently have a cursor allocated to it.

This does seem to work, without the @ notation
exec('declare crsTablesToProcess cursor for select Table_Name '
+ ' from ' + @Table_TableNames +
' where partition <> ''no''')

 

[bertrandkis]

Hello friend,
I see you are struggling with cursors and dynamic sql.The news is that those 2 things don't get along well with each other.If you want to use dynamic sql and store the result temporaly,use a temporary table or a table variable.
You see when you create your cursor like this:

exec('declare crsTablesToProcess cursor for select Table_Name '
+ ' from ' + @Table_TableNames +
' where partition <> ''no''')
This cursor is not accessible outside the EXEC because the exec creates it's own batch.All variables and cursors created in that batch are not accessible outsite the EXEC.

So I will suggest that you rewrite your query like this:
Declare @sql varchar(300)
set @sql='select Table_Name from '+@TableNamePassedAsArgument +'where partition <>''no''
--Since you already know how many columns you will need and their datatype you can create a temp table like this:
Create table #TempTable(counter int identity, col1 varchar(50))
--Now Populate your temptable
Insert into #TempTable Exec(@sql)
--As you have an identity column in your table you can loop through the table fetching one record at time
Declare @ctr int
set @ctr=1
While @ctr<=(Select Max(counter) from #temp)
Begin
--do your stuff here
set @ctr=@ctr+1
end
Note:In terms of server resources,a cursor uses more resources than a temp table.
That's all folks

 

[TheJon]

Bertrandkis makes a good point, and scores a point.

[tt]|_ |_ _ | _ _
|, | )(/, |(_)| )
'[/tt]

 

[NullTerminator]

Thanks to both for the feed back. declaring crs in exec is visible outside of exec if I do not use the @sign notation. SQL Svr 2000. I'll keep the temp table technique in mind as I move forward.

tnx
\0