Can the usage of a Cursor dynamically generate a query statement?

Hi,
As far as my understanding goes, cursors can be used to fill in variables as
it iterates through a pre-defined hardcoded query.
Would like to know if I can make a dynamic query within a cursor and
subsequently
pump values obtained from this dynamic query into their respective variables
.
Appreciate any form of reply! Thanks!
An extract of the code (it doesn't run):
DECLARE Scursor CURSOR FOR
SELECT isnull(@.account_colname, '')
, isnull(@.trans_date_colname, '')
, isnull(@.trans_time_colname, '')
, isnull(@.trans_type_colname, '')
, isnull(@.auxtrc_colname, '')
, isnull(@.auxtrc_colname, '')
, isnull(@.ref_no_colname, '')
, isnull(@.ref_no_colname, '')
, isnull(@.amount_colname, '')
, isnull(@.teller_colname, '')
, isnull(@.teller_colname, '')
, isnull(@.seq_colname, '')
-- ,ROWIDTOCHAR(rowid) --ROWID
FROM isnull(@.source_tabname, '')
WHERE isnull(CASE @.mode_day_all WHEN 'DAY' THEN @.p_trans_date END, '') +
isnull(@.erritx_colname, '') = isnull(@.erritx_flag, '')
AND isnull(@.contra_colname, '') IS NULL
OPEN ScursorHi Arthur,
The dynamic queries will be executed in a seperate memory space so u will
not be able to access any of the local variables. This holds good for your
cursors as well. u ll not be able to call them within your D-SQL.
rgds,
anu
"Arthur" wrote:

> Hi,
> As far as my understanding goes, cursors can be used to fill in variables
as
> it iterates through a pre-defined hardcoded query.
> Would like to know if I can make a dynamic query within a cursor and
> subsequently
> pump values obtained from this dynamic query into their respective variabl
es.
> Appreciate any form of reply! Thanks!
> An extract of the code (it doesn't run):
> DECLARE Scursor CURSOR FOR
> SELECT isnull(@.account_colname, '')
> , isnull(@.trans_date_colname, '')
> , isnull(@.trans_time_colname, '')
> , isnull(@.trans_type_colname, '')
> , isnull(@.auxtrc_colname, '')
> , isnull(@.auxtrc_colname, '')
> , isnull(@.ref_no_colname, '')
> , isnull(@.ref_no_colname, '')
> , isnull(@.amount_colname, '')
> , isnull(@.teller_colname, '')
> , isnull(@.teller_colname, '')
> , isnull(@.seq_colname, '')
> -- ,ROWIDTOCHAR(rowid) --ROWID
> FROM isnull(@.source_tabname, '')
> WHERE isnull(CASE @.mode_day_all WHEN 'DAY' THEN @.p_trans_date END, '') +
> isnull(@.erritx_colname, '') = isnull(@.erritx_flag, '')
> AND isnull(@.contra_colname, '') IS NULL
> OPEN Scursor
>
>|||Why cursors? Why dynamic SQL? Both are things that you should try to
avoid.
If you need more help, please specify your actual problem:
http://www.aspfaq.com/etiquette.asp?id=5006
--
David Portas
SQL Server MVP
--|||The answer is yes, and the question is why do you want to do this?. Try
finding a set based solution to the problem and leave cursors as the last
resource in your pocket.
Example:
use northwind
go
declare @.my_cursor cursor
declare @.sql nvarchar(4000)
declare @.sd datetime
declare @.ed datetime
declare @.order_id int
declare @.order_date datetime
set @.sd = '19960101'
set @.ed = '19970101'
set @.sql = N'set @.c = cursor local fast_forward for select orderid,
orderdate from dbo.orders where orderdate >= '''
set @.sql = @.sql + convert(char(8), @.sd, 112) + N''' and orderdate < ''' +
convert(char(8), @.ed, 112) + N''''
set @.sql = @.sql + N'; open @.c'
execute sp_executesql @.sql, N'@.c cursor output', @.c = @.my_cursor output
if cursor_status('variable', '@.my_cursor') = 1
begin
while 1 = 1
begin
fetch next from @.my_cursor into @.order_id, @.order_date
if @.@.error <> 0 or @.@.fetch_status <> 0 break
select @.order_id, @.order_date
end
end
close @.my_cursor
deallocate @.my_cursor
go
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
AMB
"Arthur" wrote:

> Hi,
> As far as my understanding goes, cursors can be used to fill in variables
as
> it iterates through a pre-defined hardcoded query.
> Would like to know if I can make a dynamic query within a cursor and
> subsequently
> pump values obtained from this dynamic query into their respective variabl
es.
> Appreciate any form of reply! Thanks!
> An extract of the code (it doesn't run):
> DECLARE Scursor CURSOR FOR
> SELECT isnull(@.account_colname, '')
> , isnull(@.trans_date_colname, '')
> , isnull(@.trans_time_colname, '')
> , isnull(@.trans_type_colname, '')
> , isnull(@.auxtrc_colname, '')
> , isnull(@.auxtrc_colname, '')
> , isnull(@.ref_no_colname, '')
> , isnull(@.ref_no_colname, '')
> , isnull(@.amount_colname, '')
> , isnull(@.teller_colname, '')
> , isnull(@.teller_colname, '')
> , isnull(@.seq_colname, '')
> -- ,ROWIDTOCHAR(rowid) --ROWID
> FROM isnull(@.source_tabname, '')
> WHERE isnull(CASE @.mode_day_all WHEN 'DAY' THEN @.p_trans_date END, '') +
> isnull(@.erritx_colname, '') = isnull(@.erritx_flag, '')
> AND isnull(@.contra_colname, '') IS NULL
> OPEN Scursor
>
>