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
>
>

Can someone help me in understanding the output of this TSQL script

declare @.startdate datetime

declare @.enddate datetime

declare @.testvalue datetime

set @.startdate = '2005-12-31'

set @.enddate = '2006-12-29'

set @.testvalue ='2006-05-17'

if convert(varchar(20), @.testvalue, 102) between convert(varchar(20), @.startdate, 102) and convert(varchar(20), @.enddate, 102)

print 'yes'

else

print 'no'

-

The above script print yes with format specifier as 102 where as it prints no with format specifier as 102. Why/how does the format specifier affect the output?

I agree that there are better was of achieving what is done in the above script. But I am curious to know the why sql server behaves this way in the above query.

Thanks

Take a look at the output of the following SQL:

declare @.startdate datetime
declare @.enddate datetime
declare @.testvalue datetime
set @.startdate = '2005-12-31'
set @.enddate = '2006-12-29'
set @.testvalue ='2006-05-17'

SELECT Mode = 'No Format', TestVal = convert(varchar(20), @.testvalue),
StartVal = convert(varchar(20), @.startdate),
EndVal = convert(varchar(20), @.enddate)
UNION ALL
SELECT 'Format 102', convert(varchar(20), @.testvalue, 102),
convert(varchar(20), @.startdate, 102),
convert(varchar(20), @.enddate, 102)

This gives the following results:

No Format | May 17 2006 12:00AM | Dec 31 2005 12:00AM |Dec 29 2006 12:00AM
Format 102 | 2006.05.17 | 2005.12.31 | 2006.12.29

As you can see the no format provides date text strings which do not sort alphabetically in date order at all. Format 102 produces a text string which does sort in date order when sorted alphabetically so can be used in the comparison. Of course the simple way to do it is to perform the comparison directly on the date data.

|||

I agree with your explanation for format specifier 102. However, This means that my script in the first thread should work with the format specifier as 101. But it doesnt. Can you explain this behaviour please?

|||

If you are comparing date values, you 'should NOT' be converting to varchar!

It is wasted effort and slows your process down.

|||

That's the reason i mentioned in my first post that we can do this in a better way. I am of the opinion that we have to use datetime instead of varchar(20).

But what i want to know is why does the change in format specifier (in my query) change the output value.

With 101 format specifier the expression evaluates to false where as with 102 it evaluates to true. Need to know/understand this behaviour of SQL server wrt the format specifier change.

|||

For exactly the same reason - again this is a text-based comparison.

Here are the strings you are comparing, ordered alphanumerically ascending:

--102
2005.12.31 - @.startdate
2006.05.17 - @.testvalue
2006.12.29 - @.enddate

--101
05/17/2006 - @.testvalue
12/29/2006 - @.enddate
12/31/2005 - @.startdate

It's clear to see that in the second example, @.testvalue < @.enddate. In fact using format 101 makes your BETWEEN condition impossible to meet.

Chris

|||Thanks Chris!