Dynamic Cursor Generation..

[Arijit Chatterjee]

Hi Everybody,
I have a probs with dynamic generation.
I am writing the probs
======================================
create proc test
as
declare @query varchar(500)
set @query = 'select * from table'
----------------------------------------------
declare mycur Cursor for Select * from table |
open mycur |
----------------------------------------------
but instate of above block how can I dynamically generate this query?
---------------------------------------
declare mycur Cursor for exec (@query) |
---------------------------------------
Or tell me the way.
Regards
Arijit Chatterjee

[Raghu Raman]

hi, the problem was not clear what u want to do dynamiclly.
if u want to ftech rows thru proc, u can code like

create proc test
as
use db
exec(select * from table)
this might work

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Chad Gould]

On 4 Nov 2003 21:04:27 -0800, ar*****************@yahoo.co.in (Arijit
Chatterjee) wrote:

but instate of above block how can I dynamically generate this query?
---------------------------------------
declare mycur Cursor for exec (@query) |
---------------------------------------
The way I've done stuff like this in the past is to stuff the results
into a temp table and run a cursor off that.

Example:
declare @SQLstring varchar(100)
create table #foo (foovar int)

select @SQLString = 'select distinct foovar from footable'

insert into #foo exec(@SQLString)

declare foo_cursor for select distinct foovar from #foo
etc.

Obviously you really want to do this type of stuff when there is no
other way around it.
Or tell me the way.
Regards
Arijit Chatterjee