Can I use dynamic sql in ASE to declare a cursor

I want to use dynamic sql to select the database name for a cursor. Is this or开发者_如何学编程 something similar possible using sybase ASE?

create procedure myproc
  @dbname = varchar(20) = null as

declare mycur cursor for select @dbname..mytable

... use cursor

go

You can create dynamically a temp table
something like

create procedure myproc (@dbname  ....)

as

exec ('SELECT ...... into tempdb..test FROM '+@dbName+'..mytable')

-- and then

DECLARE Cursor1 for tempdb..test
open cursor 

etc

To use dynamic sql, first create a temp table using

Create table #mytemptab (col1 …, col2 …)

Construct the dynamic sql using the following method

DECLARE @sqlstr VARCHAR(5000)

SELECT @sqlstr=’SELECT col1, col2 FROM ‘+@table

Now insert into the temp table you created (make sure it is of the same datatypes as the sql output)

SELECT @sqlstr= ‘Insert into #mytemptab (col1, col2 …) ‘+@sqlstr

Execute sql statement (to insert data into temp table)

EXECUTE (@sqlstr)

Now use the temp table in the cursor

DECLARE mycursor CURSOR FOR SELECT col1, col2 FROM #mytemptab

OPEN…

FETCH…

……