By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,130 Members | 2,228 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,130 IT Pros & Developers. It's quick & easy.

Variable inside a nested loop

P: n/a
I am trying to write a utility/query to get a report from a table. Below
is the some values in the table:
table name: dba_daily_resource_usage_v1
conn|loginame|dbname|cum_cpu|cum_io|cum_mem|last_b atch
------------------------------------------------------------
80 |farmds_w|Farm_R|4311 |88 |5305 |11/15/2004 11:30
80 |abcdes_w|efgh_R|5000 |88 |4000 |11/15/2004 12:30
45 |dcp_webu|DCP |5967 |75 |669 |11/16/2004 11:30
95 |dcp_webu|XYZ |5967 |75 |669 |11/17/2004 11:30

I need to write a query which for a given date (say 11/15/2004),
generate a resource usage report for a given duration (say 3 days).

Here is my query:
************************************
set quoted_identifier off
declare @var1 int
set @var1=0

--BEGIN OUTER LOOP
while @var1<=3 --INPUT runs the report for 3 days
begin
declare @vstartdate char (10) --INPUT starting date
set @vstartdate='11/15/2004'

--builds a range of date
declare @var2 datetime
set @var2=(select distinct (dateadd(day,@var1,convert(varchar
(10),last_batch,101)))
--set @var2=(select distinct (dateadd(day,@var1,last_batch))
from dba_daily_resource_usage_v1
where convert(varchar (10),last_batch,101)=@vstartdate)

set @var1=@var1+1 --increments a day

declare @var5 varchar (12)
--set dateformat mdy

--converts the date into 11/15/2004 format from @var2
set @var5="'"+(convert(varchar(10),@var2,101))+"'"
--print @var5 produces '11/15/2004' as result

declare @vloginame varchar (50)
declare @vdbname varchar (50)

--BEGIN INNER LOOP
declare cur1 cursor read_only for
select distinct loginame,dbname from
dba_daily_resource_usage_v1
where convert(varchar (10),last_batch,101)=@var5
--??????PROBLEM AREA ABOVE STATEMENT??????
--print @var5 produces '11/15/2004' as result
--however cursor is not being built and hence it exits the
--inner loop (cursor)
open cur1
fetch next from cur1 into @vloginame, @vdbname
while @@fetch_status=0
begin
--print @var5 produces '11/15/2004' as result
declare @vl varchar (50)
set @vl="'"+rtrim(@vloginame)+"'"
declare @vd varchar (50)
set @vd="'"+@vdbname+"'"

--processes the cursors
declare @scr varchar (200)
set @scr=("select max(cum_cpu) from dba_daily_resource_usage_v1 where
loginame="+@vl+
" and dbname="+@vd+" and "+"convert(varchar
(10),last_batch,101)="+@var5)
--set @var3 =(select max(cum_cpu) from dba_daily_resource_usage_v1
where
--loginame=@vloginame and dbname=@vdbname
--and convert(varchar (10),last_batch,101)=@var5)
print @scr
--exec @scr
fetch next from cur1 into @vloginame, @vdbname
end
--END INNER LOOP
select @var2 as "For date"
deallocate cur1
end
--END OUTER LOOP
************************************
PROBLEM:
Even though variable @var5 is being passed as '11/15/2004' inside the
cursor fetch (see print @var5 inside the fetch), the value is not being
used to build the cursor. Hence, the cursor has no row set.
Basically, the variable @var5 is not being processed/passed correctly
from outside the cursor to inside the cursor.

Any help please.
Thanks


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Sorry to answer with another question but why are you using cursors here?
You should always try to avoid using cursors as they increase the complexity
of your code and usually perform poorly. The best way to get help with your
problem is to post DDL, some sample data INSERTs and show your required end
result. See:

http://www.aspfaq.com/etiquette.asp?id=5006

That way we can help you with a query based solution instead.

--
David Portas
SQL Server MVP
--
Jul 20 '05 #2

P: n/a
On Fri, 03 Dec 2004 02:14:09 GMT, r rk wrote:
I am trying to write a utility/query to get a report from a table. Below
is the some values in the table:
table name: dba_daily_resource_usage_v1
conn|loginame|dbname|cum_cpu|cum_io|cum_mem|last_ batch
------------------------------------------------------------
80 |farmds_w|Farm_R|4311 |88 |5305 |11/15/2004 11:30
80 |abcdes_w|efgh_R|5000 |88 |4000 |11/15/2004 12:30
45 |dcp_webu|DCP |5967 |75 |669 |11/16/2004 11:30
95 |dcp_webu|XYZ |5967 |75 |669 |11/17/2004 11:30

I need to write a query which for a given date (say 11/15/2004),
generate a resource usage report for a given duration (say 3 days). (snip)PROBLEM:
Even though variable @var5 is being passed as '11/15/2004' inside the
cursor fetch (see print @var5 inside the fetch), the value is not being
used to build the cursor. Hence, the cursor has no row set.
Hi r rk,

You didn't post CREATE TABLE stattements and INSERT statements for sample
data that would allow me to test run your code. However, I already see
lots of problems. I think you should get rid of all those implicit and
explicit date/string conversions, get rid of both loops (both the
cursor-driven and the non-cursor driven) and get rid of the dynamic SQL.
That would not only solve your problem, it would improve the performance,
improve maintainability and conform to good coding practices.

Before suggesting a set-based alternative to this procedural code, allow
me to comment on the current code first:

set @var2=(select distinct (dateadd(day,@var1,convert(varchar
(10),last_batch,101)))
--set @var2=(select distinct (dateadd(day,@var1,last_batch))
from dba_daily_resource_usage_v1
where convert(varchar (10),last_batch,101)=@vstartdate)
This will set @var2 to NULL if no row exists in the table with the date in
last_batch equal to @vstartdate.
The third argument in dateadd must me of datetime datatype. Your
conversion to varchar is not necessary as it will be converted back to
datetime immediately. If you do this to get rid of the time portion, then
at least use a safe date format (112 is recommended).
--converts the date into 11/15/2004 format from @var2
set @var5="'"+(convert(varchar(10),@var2,101))+"'"
--print @var5 produces '11/15/2004' as result

declare @vloginame varchar (50)
declare @vdbname varchar (50)

--BEGIN INNER LOOP
declare cur1 cursor read_only for
select distinct loginame,dbname from
dba_daily_resource_usage_v1
where convert(varchar (10),last_batch,101)=@var5
--??????PROBLEM AREA ABOVE STATEMENT??????


You have added singe quotes to @var5 in the set statement above. This
means that for each row in dba_daily_resource_usage_v1, SQL Server will
convert the date, get something like 11/15/2004 (without quotes!!) and
compare that to @v5, which is '11/15/2004' (with quotes!!!)

Changing this would probably fix your code, but do read on, please.
Though it is probably possible to fix the errors in your code, it will
always remain slow and (as a result of using unsafe date formats) buggy.
Here is a singe set-based SELECT statement that should result in the same
rows as your procedure (though not in the exact same format, but that
would be easy to fix in the presentation tier).

DECLARE @vstartdate datetime --INPUT starting date
SET @vstartdate = '20041115'
-- NOTE: I changed the datatype for the variable
-- and the date format used for the date constant.

SELECT loginname, dbname, lb_date, MAX(cum_cpu)
FROM (SELECT loginname, dbname, cum_cpu
DATEADD(day,DATEDIFF(day,'20000101',last_batch),'2 0000101')
FROM dba_daily_resource_usage_v1
WHERE last_batch >= @vstartdate
AND last_batch < DATEADD(day, @vstartdate, 4)
-- gives report for @vstartdate up to and including @vstartdate + 3 days
) AS D (loginname, dbname, cum_cpu, lb_date)
GROUP BY loginname, dbname, cum_cpu

(untested)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 20 '05 #3

P: n/a
On Fri, 03 Dec 2004 02:14:09 GMT, r rk wrote:
I am trying to write a utility/query to get a report from a table. Below
is the some values in the table:
table name: dba_daily_resource_usage_v1
conn|loginame|dbname|cum_cpu|cum_io|cum_mem|last_ batch
------------------------------------------------------------
80 |farmds_w|Farm_R|4311 |88 |5305 |11/15/2004 11:30
80 |abcdes_w|efgh_R|5000 |88 |4000 |11/15/2004 12:30
45 |dcp_webu|DCP |5967 |75 |669 |11/16/2004 11:30
95 |dcp_webu|XYZ |5967 |75 |669 |11/17/2004 11:30

I need to write a query which for a given date (say 11/15/2004),
generate a resource usage report for a given duration (say 3 days). (snip)PROBLEM:
Even though variable @var5 is being passed as '11/15/2004' inside the
cursor fetch (see print @var5 inside the fetch), the value is not being
used to build the cursor. Hence, the cursor has no row set.
Hi r rk,

You didn't post CREATE TABLE stattements and INSERT statements for sample
data that would allow me to test run your code. However, I already see
lots of problems. I think you should get rid of all those implicit and
explicit date/string conversions, get rid of both loops (both the
cursor-driven and the non-cursor driven) and get rid of the dynamic SQL.
That would not only solve your problem, it would improve the performance,
improve maintainability and conform to good coding practices.

Before suggesting a set-based alternative to this procedural code, allow
me to comment on the current code first:

set @var2=(select distinct (dateadd(day,@var1,convert(varchar
(10),last_batch,101)))
--set @var2=(select distinct (dateadd(day,@var1,last_batch))
from dba_daily_resource_usage_v1
where convert(varchar (10),last_batch,101)=@vstartdate)
This will set @var2 to NULL if no row exists in the table with the date in
last_batch equal to @vstartdate.
The third argument in dateadd must me of datetime datatype. Your
conversion to varchar is not necessary as it will be converted back to
datetime immediately. If you do this to get rid of the time portion, then
at least use a safe date format (112 is recommended).
--converts the date into 11/15/2004 format from @var2
set @var5="'"+(convert(varchar(10),@var2,101))+"'"
--print @var5 produces '11/15/2004' as result

declare @vloginame varchar (50)
declare @vdbname varchar (50)

--BEGIN INNER LOOP
declare cur1 cursor read_only for
select distinct loginame,dbname from
dba_daily_resource_usage_v1
where convert(varchar (10),last_batch,101)=@var5
--??????PROBLEM AREA ABOVE STATEMENT??????


You have added singe quotes to @var5 in the set statement above. This
means that for each row in dba_daily_resource_usage_v1, SQL Server will
convert the date, get something like 11/15/2004 (without quotes!!) and
compare that to @v5, which is '11/15/2004' (with quotes!!!)

Changing this would probably fix your code, but do read on, please.
Though it is probably possible to fix the errors in your code, it will
always remain slow and (as a result of using unsafe date formats) buggy.
Here is a singe set-based SELECT statement that should result in the same
rows as your procedure (though not in the exact same format, but that
would be easy to fix in the presentation tier).

DECLARE @vstartdate datetime --INPUT starting date
SET @vstartdate = '20041115'
-- NOTE: I changed the datatype for the variable
-- and the date format used for the date constant.

SELECT loginname, dbname, lb_date, MAX(cum_cpu)
FROM (SELECT loginname, dbname, cum_cpu
DATEADD(day,DATEDIFF(day,'20000101',last_batch),'2 0000101')
FROM dba_daily_resource_usage_v1
WHERE last_batch >= @vstartdate
AND last_batch < DATEADD(day, @vstartdate, 4)
-- gives report for @vstartdate up to and including @vstartdate + 3 days
) AS D (loginname, dbname, cum_cpu, lb_date)
GROUP BY loginname, dbname, cum_cpu

(untested)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.